October 11, 2010 at 9:09 am
In the case of a disaster recovery we currently use the following script, (http://support.microsoft.com/kb/918992/) to transfer logins across from our live server to our backup server. The server is a generic server, used in the case of disasters so it will not necessarily be patched to the same level as the live server, hence the reason we cannot rely on restoring the master database.
The only problem we have found in tests is that this script does not allow the Sa password to be recreated on the backup server, as the sa password is not generated. (I have recently joined the company and there is a number of servers that I don’t hold the Sa password for, we are currently going through the process of changing the Sa passwords on the servers, but this will take some time)
Is there a way we can transfer the Sa password as part of this process ?
October 11, 2010 at 9:21 am
MarvinTheAndriod (10/11/2010)
In the case of a disaster recovery we currently use the following script, (http://support.microsoft.com/kb/918992/) to transfer logins across from our live server to our backup server. The server is a generic server, used in the case of disasters so it will not necessarily be patched to the same level as the live server, hence the reason we cannot rely on restoring the master database.The only problem we have found in tests is that this script does not allow the Sa password to be recreated on the backup server, as the sa password is not generated. (I have recently joined the company and there is a number of servers that I don’t hold the Sa password for, we are currently going through the process of changing the Sa passwords on the servers, but this will take some time)
Is there a way we can transfer the Sa password as part of this process ?
Research sp_help_revlogin. Plenty of information in the web.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 11, 2010 at 9:26 am
Huh? (http://support.microsoft.com/kb/918992/) is sp_help_revlogin. The issue is with regards to transferring the Sa password.
October 11, 2010 at 9:33 am
MarvinTheAndriod (10/11/2010)
Huh? (http://support.microsoft.com/kb/918992/) is sp_help_revlogin. The issue is with regards to transferring the Sa password.
sp_help_revlogin is a script provided by microsoft which scripts out logins and their passwords as binary to hex, so the passwords are never revealed.
it is the recommended way to transfer this information from one server to another; take a look again, the first 5 google links i did certainly gave examples and explanations on how to use it.
Lowell
October 11, 2010 at 9:55 am
Lowell (10/11/2010)
MarvinTheAndriod (10/11/2010)
Huh? (http://support.microsoft.com/kb/918992/) is sp_help_revlogin. The issue is with regards to transferring the Sa password.sp_help_revlogin is a script provided by microsoft which scripts out logins and their passwords as binary to hex, so the passwords are never revealed.
it is the recommended way to transfer this information from one server to another; take a look again, the first 5 google links i did certainly gave examples and explanations on how to use it.
I am well a where of sp_help_login. I linked to the microsoft article in my original and second Post! My point is it doesn't script up the Sa Password.
October 11, 2010 at 10:12 am
???
EXEC sp_help_revlogin 'sa'
/* sp_help_revlogin script
** Generated Oct 11 2010 12:10PM on DBSQL2K5 */
-- Login: sa
CREATE LOGIN [sa] WITH PASSWORD = 0x01004086CEB61FB1FE6C34DEC914CC131938263D12A9FA0B2EF7 HASHED, SID = 0x01, CHECK_POLICY = ON, CHECK_EXPIRATION = OFF
the code target server platform is SQL 2005:
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 (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
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 @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, l.hasaccess, l.denylogin
FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name )
WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, l.hasaccess, l.denylogin
FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name )
WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @hasaccess, @denylogin
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 ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
-- obtain password policy state
SELECT @is_policy_checked =
CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END
FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked =
CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END
FROM sys.sql_logins WHERE name = @name
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name )
+ ' WITH PASSWORD = ' + @PWD_string
+ ' HASHED, SID = ' + @SID_string
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login has exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
sp_help_revlogin 'sa'
Lowell
October 11, 2010 at 10:42 am
i see what you were refering to...if you run sp_rev_login with no parameters, every login EXCEPT sa is scripted.
if you pass a specific login name, it gets scripted if it exists....
from there you can see you can simple change it to ALTER LOGIN instead for the 'sa' login, since it already exists.
Lowell
October 12, 2010 at 4:19 am
Lowell (10/11/2010)
i see what you were refering to...if you run sp_rev_login with no parameters, every login EXCEPT sa is scripted.if you pass a specific login name, it gets scripted if it exists....
from there you can see you can simple change it to ALTER LOGIN instead for the 'sa' login, since it already exists.
Thanks. I have now done this, but when I change the script to ALTER LOGIN i get this error message:
Incorrect syntax near '0x01004086ceb66433e82f3f8946c27e33641a8179e4ad419c5811'.
From this script that was generated
ALTER LOGIN [sa] WITH PASSWORD = 0x01004086CEB66433E82F3F8946C27E33641A8179E4AD419C5811 HASHED, SID = 0x01, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF
October 12, 2010 at 10:04 am
MarvinTheAndriod (10/12/2010)
Lowell (10/11/2010)
i see what you were refering to...if you run sp_rev_login with no parameters, every login EXCEPT sa is scripted.if you pass a specific login name, it gets scripted if it exists....
from there you can see you can simple change it to ALTER LOGIN instead for the 'sa' login, since it already exists.
Thanks. I have now done this, but when I change the script to ALTER LOGIN i get this error message:
Incorrect syntax near '0x01004086ceb66433e82f3f8946c27e33641a8179e4ad419c5811'.
From this script that was generated
ALTER LOGIN [sa] WITH PASSWORD = 0x01004086CEB66433E82F3F8946C27E33641A8179E4AD419C5811 HASHED, SID = 0x01, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF
You cant specify the SID during ALTER LOGIN!!
To script the current SA password to an ALTER LOGIN script just use the following
SELECT 'ALTER LOGIN [SA] WITH PASSWORD = ' +
sys.fn_varbintohexstr(password_hash) +
' HASHED'
FROM SYS.SQL_LOGINS
WHERE NAME = 'sa'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 12, 2010 at 10:17 am
Thanks. Ran the T-SQL and it generated the following script:
ALTER LOGIN [SA] WITH PASSWORD = 0x01004086ceb66433e82f3f8946c27e33641a8179e4ad419c5811 HASHED, CHECK_POLICY = ON, CHECK_EXPIRATION = OFF
But it still generated the same error:
Incorrect syntax near '0x01004086ceb66433e82f3f8946c27e33641a8179e4ad419c5811'.
October 12, 2010 at 10:50 am
MarvinTheAndriod (10/12/2010)
Thanks. Ran the T-SQL and it generated the following script:ALTER LOGIN [SA] WITH PASSWORD = 0x01004086ceb66433e82f3f8946c27e33641a8179e4ad419c5811 HASHED, CHECK_POLICY = ON, CHECK_EXPIRATION = OFF
But it still generated the same error:
Incorrect syntax near '0x01004086ceb66433e82f3f8946c27e33641a8179e4ad419c5811'.
Hmm, i have just modified the code above due to one other issue, you cant specify policy check and hashed password together so i have changed the query above to reflect this, sorry my bad :blush:
With regard to the error you are getting, what version of sql server are you using?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 25, 2010 at 5:41 am
touching base on this, as Marvin mentioned trouble with this in another thread. I thought the scripting of the hashed password got him where he wanted to be.
I tested this on both 2005 and 2008.
I ran the command sp_help_revlogin sa
it produced the first command below; when I changed to the ALTER command beneath it, it works without error;
-- Login: sa ----results from sp_help_revlogin sa
CREATE LOGIN [sa] WITH PASSWORD = 0x01004086CEB6F32C5A63D73E3A921E26D19FEC8B12022DF19B0A HASHED, SID = 0x01, CHECK_POLICY = ON, CHECK_EXPIRATION = OFF
--changed, because the login already exists, just need to change the password so it matches.
ALTER LOGIN [sa] WITH PASSWORD = 0x01004086CEB6F32C5A63D73E3A921E26D19FEC8B12022DF19B0A HASHED, CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
--{edit}--
When I run the following procedure in SQL 2005
USE MASTER
ALTER LOGIN [Sa] WITH
PASSWORD = 0x01004086CEB66433E82F3F8946C27E33641A8179E4AD419C5811
HASHED, CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
I get the following error:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '0x01004086ceb66433e82f3f8946c27e33641a8179e4ad419c5811'.
However this works perfectly in SQL Server 2008. It also matches SQL server syntax on BOL. What Am I doing wrong, is there an alternative way to do this ?
Marvin that is so weird; i pasted your command to my SQL2005 instance, and it ran without an error;
maybe it's due to a patch level?
here's my @@version:
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation Express Edition
with Advanced Services on Windows NT 5.1 (Build 2600: Service Pack 2)
Lowell
Lowell
October 25, 2010 at 6:51 am
From my earlier reply.
"This query worked fine for me. Please confirm if you are connected to a SQL Server 2005 instance."
Most likely you are executing this query on a SQL Server 2000 instance.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
October 25, 2010 at 7:40 am
thanks for your help, when I rebuilt the new server I had applied the latest patch.
October 26, 2010 at 12:46 am
Since it seems so dificult to alter the sa password ....
this must be another reason NOT to use sa !
If you need sysadmin auth, which 99.99999% of the apps will not need, use windows authentication :w00t:
btw: we change the sa password +/- every month to a random generated value !
We could as well disable sa, but that wouldn't work for SQL2000.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply