Transfer Logins dan Passwords Antar Instance MS SQL Server
Pendahuluan
Terdapat beberapa masalah saat kita memindahkan database dari instance satu ke instance lainnya atau dari mesin server satu ke server lainnya atau saat melakukan upgrade side-by-side MS SQL Server dari versi lama ke versi yang baru. Salah satu masalah tersebut adalah objek login dan password tidak ikut ditransfer padahal semua data telah dipindahkan baik dengan cara backup restore maupun detach dan attach database. Setelah semua data selesai dipindahkan dan berusaha login ke database, akan muncul pesan:
Msg 18456, Level 16, State 1
Login failed for user '%ls'.
Masalah ini timbul karena data login dan password tidak disimpan dalam masing-masing database namun disimpan dalam master database sehingga perlu dilakukan pemindahan secara manual.
Transfer Login dan Password Antar Server MS SQL Server 7.0
Fasilitasn Transfer Objek pada SQL Server 7.0 Transformation Services (DTS) dapat digunakan untuk mentransfer login dan user antara dua buah sever tetapi tidak password login SQL Server tidak ikut ditransfer. Untuk mentransfer login dan password pada SQL Server 7.0 ke server lain yang menjalankan SQL Server 7.0 dapat dilakukan dengan langkah-langkah pada sesi "Solusi Lengkap untuk Transfer Login dan Password antar SQL Server dengan Versi yang Berbeda".
Transfer Login dan Password dari SQL Server 7.0 ke SQL Server 2000 atau Antar Instance SQL Server 2000
Transfer Login dan Password dari SQL Server 7.0 ke instance SQL Server 2000 atau antar dua Instance SQL Server 2000 dapat dilakukan dengan menggunakan Task Transfer Paket Login pada DTS dalam SQL Server 2000. Langkah-langkah transfer yang dilakukan sebagai berikut:
- Konek ke SQL Server 2000 tujuan, pada SQL Server Enterprise Manager pindah ke Data Transformation Services, buka folder, klik-kanan Local Packages, dan klik New Package.
- Setelah DTS package designer terbuka, klik Transfer Logins Task pada menu Task. Lengkapi informasi tentang Source, Destination dan tab Logins dengan tepat.
Penting: SQL Server 2000 tujuan tidak dapat berjalan pada SQL Server 2000 versi 64-bit. Komponen DTS untuk SQL Server 2000 versi 64-bit tidak tersedia.
Catatan: Metode DTS akan mentransfer password namun tidak mentransfer SID yang asli. Jika login tidak dibuat dengan SID asli dan user database ditransfer ke server yang baru, user database tidak akan berhubungan dengan login. Untuk mentransfer SID asli, lakukan langkah pada sesi "Solusi Lengkap untuk Transfer Login dan Password antar SQL Server dengan Versi yang Berbeda".
Transfer Login dan Password Antar Instance SQL Server 2005
Informasi mengenai transfer login dan password antar instance SQL Server 2005 dapat dilihat pada artikel Microsoft Knowledge Base:
918992
How to transfer the logins and the passwords between instances of SQL Server 2005.
Solusi Lengkap untuk Transfer Login dan Password antar SQL Server dengan Versi yang Berbeda
Metode ini dapat diterapkan untuk:
- Transfer login dan password dari SQL Server 7.0 ke SQL Server 7.0.
- Transfer login dan password dari SQL Server 7.0 ke SQL Server 2000.
- Transfer login dan password dari SQL Server 7.0 ke SQL Server 2005.
- Transfer login dan password antar server yang menjalankan instance SQL Server 2000.
- Transfer login dan password dari SQL Server 2000 ke SQL Server 2005.
Note Lihat catatan pada akhir artikel ini mengenai informasi penting tentang langkah-langkah berikut.
Untuk mentransfer login dan password antar versi SQL Server yang berbeda, ikuti langkah-langkah berikut:
- Jalankan script di bawah pada SQL Server sumber. Script ini akan membuat dua stored procedure dengan nama sp_hexadecimal dan sp_help_revlogin dalam database master. Lanjutkan langkah 2 setelah pembuatan procedure selesai.
Catatan: Procedure di bawah merupakan independen pada tabel sistem SQL Server. Struktur tabel mungkin dapat berubah antar versi SQL Server, dan pengambilan langsung dari tabel system tidak dianjurkan.
----- Begin Script, Create sp_help_revlogin procedure -----
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE BEGIN -- NT login has access
SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
PRINT @tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
IF (@xstatus & 2048) = 2048
SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
ELSE
SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END
IF (@xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @tmpstr = @tmpstr + '''skip_encryption_old'''
ELSE
SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
----- End Script -----
- Setelah stored procedure sp_help_revlogin dibuat, jalankan procedure sp_help_revlogin dari Quert Analyzer pada server sumber. Stored procedure sp_help_revlogin dapat digunakan pada SQL Server 7.0 dan SQL Server 2000. Keluaran stored procedure sp_help_revlogin adalah script login untuk membuat login dan password dengan SID asli. Simpan keluaran, dan kemudian paste dan jalankan pada Query Analyzer SQL Server tujuan. Contoh:
EXEC master..sp_help_revlogin
Sampai sejauh ini proses pemindahan login dan password berjalan lancar.
Informasi lebih jauh dapat dilihat pada
http://support.microsoft.com/kb/246133/