January 13, 2014 at 7:10 am
If I install an instance with Windows Only authentication, and then change it to Mixed Mode, if I enable the sa login, the password has already been set. What is the default? If it's generated, how secure is it? Is the password generated? What algorithm is used for that?
January 13, 2014 at 9:17 am
When you change modes the sa password is still disabled. Here read this article:http://technet.microsoft.com/en-us/library/ms188670.aspx
If Windows Authentication mode is selected during installation, the sa login is disabled and a password is assigned by setup. If you later change authentication mode to SQL Server and Windows Authentication mode, the sa login remains disabled. To use the sa login, use the ALTER LOGIN statement to enable the sa login and assign a new password. The sa login can only connect to the server by using SQL Server Authentication.
January 13, 2014 at 9:24 am
Thanks Keith I know that. I'm wondering how the password is generated. i.e. is it secure enough? I'm wondering if I have to set my own "good" password for security reasons. I'm trying to make a security assessment.
January 13, 2014 at 9:44 am
I would say that if you don't need to use the SA account leave it disabled. If you need it make your own password that is secure enough. The one generated by setup doesn't really come into play since it is disabled at first (because you picked Windows during start up) and it is still disabled after you changed authentication.
January 13, 2014 at 5:02 pm
keymoo (1/13/2014)
If I install an instance with Windows Only authentication, and then change it to Mixed Mode, if I enable the sa login, the password has already been set. What is the default? If it's generated, how secure is it? Is the password generated? What algorithm is used for that?
Unless someone changed it, the SA password is the one used when SQL Server was installed.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 14, 2014 at 2:28 am
Jeff Moden (1/13/2014)
Unless someone changed it, the SA password is the one used when SQL Server was installed.
But you're not asked to specify an SA password during setup if you select Windows authentication, are you? :unsure:
January 14, 2014 at 3:38 am
paul.knibbs (1/14/2014)
Jeff Moden (1/13/2014)
Unless someone changed it, the SA password is the one used when SQL Server was installed.
But you're not asked to specify an SA password during setup if you select Windows authentication, are you? :unsure:
Exactly, I know the risk is small, but if the instance was placed in Mixed Mode and the sa account enabled (by mistake, or a script, or something), how secure is the password? Is it easy to reverse? Is it as secure as a SHA-256 one way hash function? Am I worrying unnecessarily about vanishingly small probabilities of edge cases?
January 14, 2014 at 3:48 am
When during the install of a new SQL server instance you choose the Windows authentication , the sa login is disabled and set to an empty string.
So when you change the authentication from Windows to SQL Server , you have to enabled the sa login , but the password is set to an empty string. I tested it with SQL Server 2008, 2008 R2 and 2012.
If during the install , you choose the SQL Server ( or Mixed ) authentication , you have to provide a not empty string ( a good novelty in 2012 ) . When you change the authentication from mixed to Windows , the sa login is "disabled" but the password is kept. So , if later on , you change the authentication from Windows to mixed , the original value ( set to the install ) is always useable.
January 14, 2014 at 7:22 am
keymoo (1/14/2014)
paul.knibbs (1/14/2014)
Jeff Moden (1/13/2014)
Unless someone changed it, the SA password is the one used when SQL Server was installed.
But you're not asked to specify an SA password during setup if you select Windows authentication, are you? :unsure:
Exactly, I know the risk is small, but if the instance was placed in Mixed Mode and the sa account enabled (by mistake, or a script, or something), how secure is the password? Is it easy to reverse? Is it as secure as a SHA-256 one way hash function? Am I worrying unnecessarily about vanishingly small probabilities of edge cases?
It's been a while since I've had to do an install so I could certainly be wrong but I'm pretty sure it always asks you for an SA password. To be sure, though, I'd always worry about the SA password and disable the SA account even after giving it a good, strong password and storing it in a safe somewhere.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 14, 2014 at 8:22 am
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?
January 14, 2014 at 9:09 am
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.
January 14, 2014 at 9:11 am
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.
January 14, 2014 at 10:44 am
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?
January 14, 2014 at 12:54 pm
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
January 14, 2014 at 12:57 pm
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?
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply