Migrating To A New Server : Moving Logins
Introduction
Moving logins to a new server is always a challenge. And there are plenty of articles on moving to a new server, moving database, etc. However I still see questions posted (one as I write this) and people are still confused, so I'll bring my perspective and experience to my methods for migrating to a new server and give you another resource.
In this series, I'll look at a few different items that I've dealt with in moving to a new server. These techniques have been used to move to a new server as well as replace an existing server that reached the end of it's lease or needed to be upgraded with new hardware. I'll try to cover both SQL 7 and 2000 as well.
Moving Logins
Logins are the big gotcha when changing servers. Because of passwords, they are the wildcard that everyone has problems with. Not to mention the fact that they are aliased or mapped in each database for security purposes. So how do you move them?
In SQL Server 2000, there are a number of ways, all of which are fairly simple to implement and work with. If you are moving between SQL Server 2000 servers, then the easiest way, and the way I recommend is to use DTS. In SQL Server 2000, there is a transfer logins task that has this icon:
If you drag this task onto your package, you will get a three tabbed dialog box. The first and second tabs are simple enough that I'm not making screen shots. They are the source (which has the logins) and the destination (where you want the logins).
The last tab is the one where you do all the configuration. It looks like the one below:
From this screen you can choose to move all logins or just those associated with a particular database. If you change to select databases, you can choose multiple databases. Once you run this, it moves all your logins to the new server, with their passwords.
Replacing the server? Trying to recover master? You can build a temporary dummy database on a spare server and transfer the logins there, then move them back to the new server.
Another method that I have used before is detailed in Q246133 (http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B246133). This procedure takes advantage of the fact that the sp_addlogin stored procedure has a parameter called @encryptopt, which can be set to 1 of three values.
| NULL | This is the default that most people are probably used to since they don't pass it in. It specifies the value that is passed in for the password should be encrypted before it is stored in the table. |
| skip_encryption | This option tells SQL Server that the value passed in for the password is already encrypted. Therefore it should just be stored in the syslogins table as is. |
| skip_encryption | This option tells SQL Server that the value passed in for the password is already encrypted, but using the algorithm from a previous version of SQL Server. Therefore it should just be stored in the syslogins table as is. |
The Q article contains a script to create to stored procedures (one calls the other). These stored procedures will generate a script that contains the login name from syslogins along with the encrypted password. When you run this script on the source (old) server, it will consist of sp_addlogin statements with the encrypted password and @encryptopt set to "skip_encryption". If you copy this script and execute it on the new server, it will create your logins.
Conclusions
No great enlightenment this time, but I hope I've made this a simple process for someone looking to move logins. I know you may have read about this before, but I'm amazed how many people resort to restoring master rather than using one of these easy techniques.
As always, I welcome your feedback and comments.
Steve Jones
©dkRanch.net March 2003