Hot Standby Security Configuration for Operational Reporting

In my last post I talked about how I used SQL Server 2005 log shipping to create a hot standby of the Airframe production database to be used for both disaster recovery and as a data source for operational reporting. In this post I'll discuss how to configure security on the production and hot standby servers to enable operational reporting.

Both servers (production and hot standby) are hosted outside the firewall. The SQL Server 2005 Reporting Services (SSRS) installation for the operational reports will be hosted inside the firewall. This means that the data source used by SSRS to connect to the hot standby database will have to use SQL Server authentication. It also means that the server hosting the SSRS operational reports will need the Microsoft Firewall Client for ISA Server 2004 installed since the hot standby server is outside the firewall.

This presents a tricky security configuration problem, namely how to configure a SQL Server login and database user on the production server that can be used to connect to the database on the hot standby server. Database users added to the production database will show up automatically on the hot standby database via log shipping. Logins however are in the master database and need to be added manually on the hot standby server.

My first attempt was to create SQL Server authentication logins on both servers with the same name. Then I added a database user for the login to my production database, and waited for the next log shipping cycle to occur for the database user to show up on the hot standby server. This didn’t work. After the next log shipping event occurred, I found that the database user in the hot standby database had no associated login and couldn’t be used to connect to the database.

After some investigation I realized I had a SID problem. Every SQL Server login has a unique identifier known as a SID, which stands for security identifier. The SID is used to associate logins in the master database with database users. Logins that use Windows authentication get their SID from Windows, but logins that use SQL Server authentication have their SID’s generated by default using a certificate that is unique to the SQL Server instance. This means that even though my logins had the same name on the production and hot standby servers, they had different SID’s. Since database users are mapped to logins using SIDs, the database user in the hot standby database was orphaned and unusable.

Here’s the work-around I used to get security properly configured:

1. Production SQL Server (outside firewall)

a. Set server authentication to “SQL Server and Windows Authentication mode

b. Create a new SQL Server login named reporting using SQL Authentication

c. Add a new user to the production database named reporting associated with the login named reporting created in the previous step

d. Add the reporting user to the db_datareader role which permits the user to read from all user tables in the database without having to grant explicit permissions (I’ll talk about why I did this in a future post)

e. Get the SID for the reporting login using the following query:
SELECT [SID]
FROM [master].[sys].[sql_logins]
WHERE [name] = 'reporting'

2. Hot Standby SQL Server (outside firewall)

a. Set server authentication to “SQL Server and Windows Authentication mode

b. Manually create a new SQL Server login using the following TSQL Command where somesid is the varbinary value of the SID retrieved in step 1-e above:
CREATE LOGIN reporting
WITH PASSWORD = 'somepassword’ ,
SID = somesid 

c. Wait for next log shipping cycle to occur

d. Check the reporting user in the hot standby database to make sure it is associated with the reporting login created in step 2-b above. This can be done by viewing the properties for the user in SQL Server Management Studio and confirming the association with the login name reporting. If the properties screen indicates “Without login” you probably didn’t use the right SID.

In my next post I’ll talk about the security configuration of the operational reporting server inside the firewall which will be used to host the SSRS reports that target the hot standby data source.