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 12»»

What is the default sa password? Expand / Collapse
Author
Message
Posted Monday, January 13, 2014 7:10 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 9:16 AM
Points: 106, Visits: 464
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?


Post #1530283
Posted Monday, January 13, 2014 9:17 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 9:14 PM
Points: 564, Visits: 851
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.




Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1530347
Posted Monday, January 13, 2014 9:24 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 9:16 AM
Points: 106, Visits: 464
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.


Post #1530350
Posted Monday, January 13, 2014 9:44 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 9:14 PM
Points: 564, Visits: 851
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.



Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1530359
Posted Monday, January 13, 2014 5:02 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 36,724, Visits: 31,173
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1530505
Posted Tuesday, January 14, 2014 2:28 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:06 AM
Points: 1,610, Visits: 5,479
Jeff Moden (1/13/2014)
[quote]
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?
Post #1530574
Posted Tuesday, January 14, 2014 3:38 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 9:16 AM
Points: 106, Visits: 464
paul.knibbs (1/14/2014)
Jeff Moden (1/13/2014)
[quote]
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?


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?



Post #1530592
Posted Tuesday, January 14, 2014 3:48 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, June 26, 2014 5:39 AM
Points: 728, Visits: 277
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.
Post #1530596
Posted Tuesday, January 14, 2014 7:22 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 36,724, Visits: 31,173
keymoo (1/14/2014)
paul.knibbs (1/14/2014)
Jeff Moden (1/13/2014)
[quote]
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?


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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1530669
Posted Tuesday, January 14, 2014 8:22 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 9:14 PM
Points: 564, Visits: 851
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?




Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1530712
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse