Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What is the default sa password?


What is the default sa password?

Author
Message
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36050 Visits: 18736
Keith Tate (1/14/2014)
I'm not sure what is being asked now? There is no default password that I know of for every instance. I'm also not sure how strong the password is that is supplied during setup (with Windows only), but why do we care at this point? The advice is to create your own strong password for sa and disable the account if it is not being used.

Is there something I'm missing?


I think Patrick noted it. It's set to an empty string if not specified during install. If I remember the install for 2012 correctly, if you do not choose mixed mode, no pwd is entered.

This is a bad idea. Personally I'd say always choose mixed mode, enter a random password if you don't need it, and then change to Windows only once you complete the install.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Nadrek
Nadrek
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1029 Visits: 2673
Try looking at the password, then.


You can start with something like this:

See my later post - remember, SHA1 is 160 bits, SHA-256 is 256 bits, and SHA-512 is 512 bits.



If it still starts with 0x0200, it's the 2012 format, which is a decent random salt with a pathetic single iteration of SHA-512 on the UCS-2 "Unicode" version of that password, so weak passwords are not secure, nor are moderately strong passwords. Use only truly strong, completely random passwords, length 15 or higher.

If you want to prove to yourself it's SHA-512, then, assuming the above code works in 2014, create a temporary account, assign it a password, and then enter that password in the HASHBYTES lines in the code above; if you get the same hash, you've provably reconstructed the SQL Server hashing algorithm.
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36050 Visits: 18736
Do you mean?

SELECT sl.name
, sp.type
, sl.sysadmin
, CAST(sl.password AS VARBINARY(384)) AS EntireSaltAndPasswordHash_HashcatFormat
, LOGINPROPERTY(sl.name,'PasswordHash') AS EntireSaltAndPasswordHashAnotherWay
, CAST(LEFT(RIGHT(sl.password,12),2) AS VARBINARY(32)) AS Salt
, HASHBYTES('SHA1', CONVERT(VARBINARY,N'computer') + CAST(LEFT(RIGHT(sl.password,12),2) AS VARBINARY(32))) AS HashBytesReconstructionOfPasswordHashFromAGivenPassword2005
, HASHBYTES('SHA2_512', CONVERT(VARBINARY,N'computer') + CAST(LEFT(RIGHT(sl.password,12),2) AS VARBINARY(32))) AS HashBytesReconstructionOfPasswordHashFromAGivenPassword2012
, HASHBYTES('SHA2_512', CONVERT(VARBINARY,N'computer') + CAST(LEFT(RIGHT(N'MyPassword',12),2) AS VARBINARY(32))) AS Pwd
FROM sys.syslogins sl
LEFT OUTER JOIN sys.server_principals sp
ON sp.sid = sl.sid
WHERE sl.password IS NOT NULL



I'm not getting matching values. Or is it not SHA2_512 in 2012?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Nadrek
Nadrek
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1029 Visits: 2673
It's SHA-512; however, SHA-512 is longer than SHA1!

EDIT: Use the version from my post later on in this thread

-- 2005 through 2012+ variants
SELECT sl.name
, sp.type
, sl.sysadmin
, CAST(sl.password AS VARBINARY(384)) AS EntireSaltAndPasswordHash_HashcatFormat
, LOGINPROPERTY(sl.name,'PasswordHash') AS EntireSaltAndPasswordHashAnotherWay
, CAST(RIGHT(LEFT(sl.password,3),2) AS BINARY(4)) AS Salt
, HASHBYTES('SHA1', CONVERT(VARBINARY,N'Password123') + CAST(LEFT(RIGHT(sl.password,12),2) AS VARBINARY(32))) AS HashBytesReconstructionOfPasswordHashFromAGivenPassword2005
, HASHBYTES('SHA2_512', CONVERT(VARBINARY,N'Password123') + CAST(LEFT(RIGHT(sl.password,34),2) AS VARBINARY(32))) AS HashBytesReconstructionOfPasswordHashFromAGivenPassword2012
FROM sys.syslogins sl
LEFT OUTER JOIN sys.server_principals sp
ON sp.sid = sl.sid
WHERE sl.password IS NOT NULL



Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36050 Visits: 18736
I guess I'm saying if I put the known password in there, the last value, it doesn't return what the value is in the system tables.

Is there some other salt being included?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Nadrek
Nadrek
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1029 Visits: 2673
Did you try the script I just posted? The original had an error; I'll update that post.

No, there's just the one salt, 4 bytes long.
SQL2012:
0x0200
ABCDEF12 - salt
xxxxx - SHA-512 hash (512 bits)

And for SQL 2005-2008R2:
0x0100
ABCDEF12 - salt
xxxxx - SHA-1 hash (160 bits)

And pwdencrypt() boils down to SHA-x(UCS-2/"Unicode" version of password + salt) - note that the salt comes second.
Andreas.Wolter
Andreas.Wolter
SSC-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

Group: General Forum Members
Points: 155 Visits: 1056
Steve Jones - SSC Editor (1/14/2014)
Is there something I'm missing?


I think Patrick noted it. It's set to an empty string if not specified during install. If I remember the install for 2012 correctly, if you do not choose mixed mode, no pwd is entered.

This is a bad idea. Personally I'd say always choose mixed mode, enter a random password if you don't need it, and then change to Windows only once you complete the install.


Well, it is correct, that entering your own password is the best idea

But I can also assure you, that SQL Server does NOT use an EMPTY password for the sa Account by Default during setup. This was prohibited since 2000 SP4 if I am not mistaken.
And since 2005 at up to now, if you do not specify mixed mode, SQL Server will auto-generate a RANDOM password - not a default password. Microsoft actually did learn from some mistakes (not looking at Oracle with "ORA", am I? ;-D )

And just for completeness: Yes, SQL Server onwards uses 256 bit SHA2 for hashing, while SQL 2008/R2 used SHA1 with 160 bits. So complexity does matter.

Andreas

---------------------------------------------------
MVP SQL Server
Microsoft Certified Master SQL Server 2008
Microsoft Certified Solutions Master Data Platform, SQL Server 2012
www.insidesql.org/blogs/andreaswolter
www.andreas-wolter.com
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8780 Visits: 16554
It's simples, the only sure fire thing to do when performing the change from windows to mixed is to issue this staright after

ALTER LOGIN [sa] WITH PASSWORD = 'somelongpassword';
ALTER LOGIN [sa] DISABLE;



-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Nadrek
Nadrek
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1029 Visits: 2673
Ok, a slightly improved version of the script above, with a CASE statement that can validate password guesses, and which that should make things much more clear.


--If you need a test user, use this:
--CREATE LOGIN test_SQLPWHashTest_imEdHJyM WITH PASSWORD = '1#i5?^@v0uz1nzE\U^E}q6Gb):u#}0z~[cqW+d\CX!q:Uv1%/182)jV='

DECLARE @pwd VARCHAR(128)
DECLARE @sql NVARCHAR(4000)
SET @pwd = 'gMNaH,;b%1hOc#e$wf&A=AftZ+EPk0fqFx17B.15XK9-ZL;W{(BiVO'

SET @sql = 'ALTER LOGIN test_SQLPWHashTest_imEdHJyM WITH PASSWORD = ''' + @pwd + ''''
EXEC(@sql)
--SET @pwd = '!YA/b.(r7TALA9;o)7wm77fI#,qq,I6tjp)E}fs5l=+A:C[G#UkRPx/oERjjmP|fdxcrclh5gQ@P2*gg6jH^vOv3[e-&Z~Fng(Aror15/n#(=#[b}UK+Otb*)axaw2wU'

SELECT sl.name
, sp.type
, sl.sysadmin

, CASE
WHEN HASHBYTES('SHA1', CONVERT(VARBINARY(256),CONVERT(NVARCHAR(128),@pwd)) + CAST(RIGHT(LEFT(sl.password,3),2) AS BINARY(4))) = CAST(RIGHT(sl.password,10) AS BINARY(20)) THEN 'SQL2005Guessed'
WHEN HASHBYTES('SHA2_512', CONVERT(VARBINARY(256),CONVERT(NVARCHAR(128),@pwd)) + CAST(RIGHT(LEFT(sl.password,3),2) AS BINARY(4))) = CAST(RIGHT(sl.password,32) AS BINARY(64)) THEN 'SQL2012Guessed'
ELSE 'NotGuessed'
END

, CAST(sl.password AS VARBINARY(384)) AS EntireSaltAndPasswordHash_HashcatFormat
, LOGINPROPERTY(sl.name,'PasswordHash') AS EntireSaltAndPasswordHashAnotherWay
, CAST(LEFT(RIGHT(sl.password,12),2) AS BINARY(4)) AS Salt2005
, CAST(LEFT(RIGHT(sl.password,34),2) AS BINARY(4)) AS Salt2012
, CAST(RIGHT(LEFT(sl.password,3),2) AS BINARY(4)) AS Salt
, UPPER(RIGHT(sys.fn_varbintohexstr(CAST(RIGHT(LEFT(sl.password,3),2) AS BINARY(4))),8)) AS SaltPure
, CAST(RIGHT(sl.password,10) AS BINARY(20)) AS PasswordHash2005
, CAST(RIGHT(sl.password,32) AS BINARY(64)) AS PasswordHash2012
, UPPER(RIGHT(sys.fn_varbintohexstr(CAST(RIGHT(sl.password,10) AS BINARY(20))),40)) AS SQL2005_HashPure
, UPPER(RIGHT(sys.fn_varbintohexstr(CAST(RIGHT(sl.password,32) AS BINARY(64))),128)) AS SQL2012_HashPure
, UPPER(RIGHT(sys.fn_varbintohexstr(CAST(RIGHT(sl.password,10) AS BINARY(20))),40)) + ':' + UPPER(RIGHT(sys.fn_varbintohexstr(CAST(LEFT(RIGHT(sl.password,12),2) AS VARBINARY(32))),8)) AS SQL2005_2008R2_OCLHashCatLiteFormat
, UPPER(RIGHT(sys.fn_varbintohexstr(CAST(RIGHT(sl.password,64) AS VARBINARY(70))),128)) + ':' + UPPER(RIGHT(sys.fn_varbintohexstr(CAST(LEFT(RIGHT(sl.password,64),3) AS VARBINARY(70))),8)) AS SQL2012_OCLHashCatLiteFormat
, HASHBYTES('SHA1', CONVERT(VARBINARY(256),CONVERT(NVARCHAR(128),@pwd)) + CAST(RIGHT(LEFT(sl.password,3),2) AS BINARY(4))) AS HashBytesReconstructionOfPasswordHashFromAGivenPassword2005
, HASHBYTES('SHA2_512', CONVERT(VARBINARY(256),CONVERT(NVARCHAR(128),@pwd)) + CAST(RIGHT(LEFT(sl.password,3),2) AS BINARY(4))) AS HashBytesReconstructionOfPasswordHashFromAGivenPassword2012
FROM sys.syslogins sl
LEFT OUTER JOIN sys.server_principals sp
ON sp.sid = sl.sid
WHERE sl.password IS NOT NULL
AND sl.name LIKE '%test%'

--If you created a test user, use this:
--DROP LOGIN test_SQLPWHashTest_imEdHJyM


rheeler2
rheeler2
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 7
Exactly! Installed Sql Server 2014 eval. Uninstalled and reinstalled. Never asked me for a password. Give windows password. Fails. Tried to reset via sqlcmd. Assured instance was Windows Authentication. Nothing works. Cannot complete install. Pls help. Thanks loads.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search