January 14, 2014 at 1:01 pm
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.
January 14, 2014 at 5:39 pm
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
January 15, 2014 at 7:36 am
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" 😉
January 17, 2014 at 11:41 am
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
August 9, 2014 at 8:55 pm
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.
August 10, 2014 at 4:01 am
Hello rheeler2,
To connect to your SQL Server the first time after its install , it is better :
- to connect to the computer on which the SQL Server , you must use the windows user you have used to install your SQL Server ( as this user is the creator of the instance , you should not have problem )
- with this "creator" windows user , you should not provide the password ( advantage of Windows authentication )
The easiest ( and quickest ) way is to add the permissions of connect to the database engine to the "creator" user and to enable this user ( right-click on the user , click on properties in the contextual menu and select the Status page ). Also , you have to give to this user some more permissions like dbcreator , public and sysadmin ( page Server Roles )
In the User Mapping , you have to give some special permissions , like dbcreator , dbdatareader , dbdatawriter
October 30, 2014 at 7:53 pm
After switching to mixed mode, it's a good practice to set a new and secure password for the SA account, and the default password is insecure (I also don't know what the default password is). If you don't want to use the SA account, just keep the account disabled as it was.
Just as this article[/url] explains, the old versions of SQL Server 2008/2005/2000 use only the SHA1 hashing algorithm to encrypt the password whereas SQL Server 2014/2012 have moved to use a stronger algorithm SHA512.
Additionally, you can also enable the password policy for your SQL Server account, which gets your account to auto lockdown after a certain number of invalid logon attempts.
November 20, 2014 at 4:04 am
If you have the login credentials of administrator account, then you can change the password of your user account very easily. You can search steps on the internet. It is very easy.
But if you want to reset password of your Admin account, then you need to first create another SA account and reset the password by following these steps:
1. Press Start+R, type sqlcmd and press Enter
2. Execute following SQL statement to add new or existing account to the SA server:
EXEC sp_addsrvrolemember ‘DOMAIN\Username’, ‘sysadmin’;
GO
Note: replace the username with the name you want to add.
3. Now, open MS SQL Server Management Studio. Login with the new account.
4. Navigate to DB > Security > Logins
5. Open the properties of SA account and reset the password.
You can more informartion for the tools visit :- http://sonikarawat.wordpress.com/2014/07/01/how-to-unlock-sql-server-account/[/url]
November 24, 2014 at 3:39 am
March 8, 2015 at 7:32 pm
The default password for the sa login will depend on the version of SQL Server installed and if you are using SQL Express or a pruchased version of SQL Server that we have installed for you. Just as this article shows, For SQL Server Express users, once you are logged in, please change your password.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply