Thursday, February 11, 2016

Export/Import MS SQL login/passwords between MS SQL servers/instances

Sometimes you need to transfer (export import) login and passwords from one Microsoft SQL Server to another or from one Microsoft SQL Instance to another. Microsoft published KB article how to solve this task kb918992 with few methods.

Here is common example scenario when this procedure is really helpful:

  • You have an application with Database backend on Microsoft SQL database
  • Database backend is Microsoft SQL AlwaysOn Availability Group
  • Application is not aware of Always On Availability Group and maybe is not designed initially for Always On (or your custom developed application)
  • Application installer automatically generate mssql login/password during its deployment
Such "not aware" application installer creates login/passwords only on one active Microsoft SQL Server/Instance and doesn't create on other nodes participating in AlwaysOn Availability Group. After SQL AlwaysOn Availability Group Failover such application will stop functioning because it cannot authenticate against Microsoft SQL Server/Instance with missing login/password. You need to transfer them manually.

Well, if you know login/password on previously active node that is not really a problem for you and usage case for kb918992. But what if Application creates auto-generated mssql login and secure random password which is unknown? Then "method 3" of kb918992 is your solution.
Here is list of such applications:
  • WebSitePanel,
  • MSPControl,
  • Dell Enterprise Manager
Mentioned applications were successfully tested and are used with SQL AlwaysOn Availability Groups using kb918992