How to configure Database mirroring for use with ODBC applications?

[mssql-support]

First published on MSDN on Jun 22, 2007

If you have an ODBC application that is connecting to a mirrored database and if

• 
  
  The application uses a connection string without a database name ( for e.g: dsn=ssistest;uid=ssistest;pwd=*********)


• 
  
  The DSN is configured to use SQL authentication with a non-blank password.


• 
  
  The default database in the DSN configuration is changed to the mirrored database.

You will see the following error message when the database is failed over from primary to secondary

ERROR [28000] [Microsoft][SQL Native Client][SQL Server]Login failed for user 'testappuser'.

ERROR [42000] [Microsoft][SQL Native Client][SQL Server]Cannot open database "mirrordb" requested by the login. The login failed.

Cause: Though you create the same user account on both the servers with the same password, upon failover the sids of the primary and the secondary do not match and hence the error.

There are a couple of ways to work around the problem:

1)      Change the connection string to include the database name. For eg: dsn=ssistest;uid=ssistest;pwd=*********;database=mirrordb;

2)      If this is not an option then you can use the SSIS transfer logins task to transfer the logins between primary and secondary.

Here are the details:

1)    Create a login on the primary server (for eg: ssistest with a password of Password1) and set the default database to master.

2)       Open SQL Server BI Studio (Business intelligence studio under SQL server program group) and create a SSIS package as per the following steps:

a)      Add two SMOServer Connection managers (one for the primary and one for the secondary).

b)      Add the Transfer Logins Task from the Toolbox and double click on it to get the properties screen.

c) Click on the Logins List and choose the ssistest user that we had setup for the mirroring.

d) In the properties of the Transfer Logins Task, under ‘Misc’ section ensure that we have 'CopySids’ set to True (This is very important)

e)      Now execute the task. You should see that the package executed successfully and transferred the logins.

3)      Now on the secondary server you should see the ssistest login. This will be in disabled state. You need to enable the same and set the password to the same one as on the primary. (Transfer logins task does this by design and the login on the destination server is assigned a random password)

4)      Now on the primary server change the default database of ssistest to mirrordb and make him a dbo in that database.

5)      Now failover the database to secondary.

6)      Change the default database of ssistest on the secondary to mirrordb (we can see that he will be automatically mapped to dbo role).

7)      Failover the mirrordb back to primary.

8)      Now create a ODBC DSN mapped the usual way

9)      Test the application It should work without a problem.(My app has the connection string the following way: dsn=ssistest;uid=ssistest;pwd=*********;)

10)      Failover the mirrodb and test your connection again from your application. It will still work.

The only caveat is that after the Test connection from ODBC administrator does not work after you failover the database from primary to secondary unless you just leave it at the last property page (Test datasource page) while you failover.

Here are some more references on why this problem is happening only with SQL logins.

1)      From Database Mirroring in SQL Server 2005 http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx#EKGAG

+++++

Usually it will take more time to redirect an entire application from the old principal to the new principal than a database mirroring automatic failover will take. The application must detect and retry connections, which may add some time to the process. In addition, if new logins using SQL Server authentication have been added to the servers, you may need to map those logins to the new principal using the system stored procedure sp_change_users_login . Complete application failover may also be delayed if any critical objects on the old principal, such as SQL Agent jobs, have not also been copied to the new principal server. (For more information, see "Preparing the Mirror server for Failover" in the Implementation section later.)

+++++++++

2) https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125975&wa=wsignin...

3)      Database Mirroring Best Practices and Performance Considerations

++++++

Make sure that applications can connect and execute all necessary actions, and that all active SQL Server logins (and their permissions) on the principal server are also present on the mirror server. You can use the Transfer Logins task of SQL Server 2005 Integration Services to accomplish this. (For details on the Transfer Logins task, see SQL Server 2005 Books Online.)

++++++++++

For more information on Transfer Logins Task:

·         Transfer Logins Task http://technet.microsoft.com/en-us/library/ms137870(SQL.90).aspx

So to summarize, the problems that we are running into from application perspective with SQL logins are expected and documented and we do need to workaround them using the ‘Transfer Logins’ task.

(For screenshots of the process and a sample SSIS package, please download the attached files).

Ramu Konidena

Support Engineer, SQL Developer team.

Sampledts_screenshots.zip