Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

What is the default sa password? Expand / Collapse
Author
Message
Posted Tuesday, January 14, 2014 9:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:25 PM
Points: 31,279, Visits: 15,740
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
Post #1530741
Posted Tuesday, January 14, 2014 9:11 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 12:06 PM
Points: 887, Visits: 2,453
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.
Post #1530743
Posted Tuesday, January 14, 2014 10:44 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:25 PM
Points: 31,279, Visits: 15,740
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
Post #1530784
Posted Tuesday, January 14, 2014 12:54 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 12:06 PM
Points: 887, Visits: 2,453
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


Post #1530846
Posted Tuesday, January 14, 2014 12:57 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:25 PM
Points: 31,279, Visits: 15,740
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
Post #1530848
Posted Tuesday, January 14, 2014 1:01 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 12:06 PM
Points: 887, Visits: 2,453
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.
Post #1530850
Posted Tuesday, January 14, 2014 5:39 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 21, 2014 11:24 AM
Points: 153, Visits: 981
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
Post #1530920
Posted Wednesday, January 15, 2014 7:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:46 AM
Points: 6,619, Visits: 14,185
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"
Post #1531103
Posted Friday, January 17, 2014 11:41 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 12:06 PM
Points: 887, Visits: 2,453
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

Post #1532207
Posted Saturday, August 9, 2014 8:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, August 23, 2014 9:37 PM
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.
Post #1601543
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse