| Downloads |
|---|
| 7777.zip |
I need to move the passwords from hundreds of users and logins from Server A to Server B. I know that the SQL Server Transfer Manager doesn't take over the passwords (although I’ll be using that to transfer the databases). I tried to use bulk copy program (bcp) to copy the master..syslogins (except sa, etc.) and to copy the passwords to the new server, but that didn’t work. Do you have any pointers or suggestions?
The answer depends on which version of SQL Server you’re running. You don’t say which version, but we’ll assume you’re running SQL 6.5 because Transfer Manager doesn’t exist in SQL Server 7.0. Just to be safe, we’ll provide some information for both platforms.
In "How Safe Are Your SQL Server Passwords?" (Windows NT Magazine, May 1997) Brian discussed some of SQL Server 6.5’s password issues. Here’s a brief excerpt that shows how you can easily transfer encrypted passwords between SQL Server 6.5 servers. You can read the complete article at www.winntmag.com.
The Transfer database option in SQL 6.5’s Enterprise Manager doesn’t let you move logons from one server to another and keep passwords intact. Instead, the option transfers logons as NULLs. This feature makes using the Transfer Manager to transfer databases difficult in a production environment because people have to manually re-establish their passwords. That’s bad enough, but there’s also a window of vulnerability while the passwords are being re-established.
You can circumvent this problem. A little experimentation shows that the pwdencrypt encryption algorithm is not server-dependent. So you can grab the encrypted passwords from one server and update the password column in syslogins on another server. sp_TransferPasswords is a simple stored procedure that copies encrypted passwords from one server to another. The sp_TransferPasswords procedure doesn’t include any error checking or reporting and makes direct updates to system tables, so use it with extreme caution. Don’t run sp_TransferPasswords unless you’ve read the source code and understand what it does.
I’ve used this technique to transfer passwords but always on servers of the same type, that is, the same version, sort order, character set, and hardware platform. The procedure pwdencrypt might use different algorithms on different server types, so you should experiment with this technique if you are considering using this technique to transfer passwords between dissimilar servers.
Run sp__TransferPasswords on the target server where you want to update password information. The name of the source server containing the correct passwords is a mandatory parameter. This technique requires that you configure the two servers as remote servers for each other. SQL Server Books Online (BOL) explains how to configure remote servers.
In some cases, you might not want to set up the remote server access or you might want to transfer a password for only one user. You can create a new procedure called sp_passwordNoEncrypt by cloning sp_password and removing the call to pwdencrypt in the update statement. After you make this change, the sp_password clone no longer encrypts the password string you enter, so you manually set the password on your new server to the encrypted version on the original server. Users can enter their old passwords on the new server and never know the difference. (Beware: Messing around with system tables and procedures is not for the faint of heart.)
SQL Server 7.0 makes the transfer process much easier. There’s no need to hack the system table because sp_addlogin directly supports the ability to move the encrypted version of a password from one machine to another by using sp_addlogin’s @encryptopt. The following snippet from BOL demonstrates this technique:
"This example (in Listing 1) creates a SQL Server login for the user Margaret with a password of Rose on Server1, extracts the encrypted password, and then adds the login for the user Margaret to Server2 using the previously encrypted password, but does not further encrypt the password. User Margaret can then log in to Server2 using the password Rose.
-- Server1
EXEC sp_addlogin Margaret, Rose
--Results
New login created.
-- Extract encrypted password for Margaret
SELECT CONVERT(VARBINARY(32), password)
FROM syslogins
WHERE name = 'Margaret'
--Results
------------------------------------------------------------------
0x2131214A212B57304F5A552A3D513453
(1 row(s) affected)
-- Server2
EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453,
@encryptopt = 'skip_encryption'
Moving logins between SQL Server 7.0 servers is even simpler if you’re using Windows NT Authentication to connect to SQL Server. SQL Server users don’t have separate passwords to access the SQL Server, so the problem doesn’t exist in the first place. Of course, this only works if both SQL Servers are in the same Windows NT domain.
