FAQ

FAQ: SQL Server passwords

Troubleshoot SQL Server passwords with these frequently asked questions from our readers. See how to recover lost passwords, create new passwords and change your SQL Server passwords with advice from our SQL Server experts.

    Requires Free Membership to View

Frequently Asked Questions:

SQL Server passwords

  1. How can I fix an application that isn't resolving username/password?
  2. Why can't I log in with 'SA' and 'password'?
  3. How do I change the SQL Server password from the control panel?
  4. Can I change the 'SA' password without my old password?
  5. How can I create a username/password for an SQL Server database?
  6. What is the best way to encrypt password fields?
  7. What is the password for 'SA' for an SQL Server 2000 first install?
  8. Can I recover my password in SQL Server 2000?
  9. How can I set a password on a database in SQL Server 7.0?
  10. Can I find out which password I have assigned to the user schema?

1. How can I fix an application that isn't resolving username/password?

If you can connect to SQL Server and access the user database using Query Analyzer from the client machine logged in as the same windows user who executes the application, the problem is surrounding the way the application is connecting to SQL Server rather than any general SQL Server authorization set up issue.

Forgive me if I am stating the obvious but it must be cleared up. For the application to use Windows Authentication it must have an option to choose between Windows Authentication or SQL Server Authentication. You do NOT type in the username and password of your Windows User Account into the application to connect to SQL Server using Windows Authentication. You only type in a username and password into an application to use SQL Server Authentication, the Windows Authentication information is based on the user who you are currently logged into Windows as.

For the application to connect to using SQL Server using Windows Authentication it will have to send a different connection string than what it sends when connecting using SQL Server Authentication. Check with your developer to ensure that the correct connection string is being set within the application.

I would also run SQL Server Profiler and trace the "SecurityAudit:Login" and "SecurityAudit:Login Failed" events. From this trace you can see 1) If the application is even trying to connect to SQL Server when the application is using Windows Authentication and 2) If what authorization credentials are being sent to SQL Server.
—Tony Bain, SQL Server Expert

Return to password FAQs

2. Why can't I log in with 'SA' and 'password'?

That is because someone has set the security for the server to accept integrated only. When set that way, you can not log in using a standard SQL Server login.
—Michael Hotek, SQL Server Expert

Return to password FAQs

3. How do I change the SQL Server password from the control panel?

I decided to change my SQL Server service password from control panel --> services, but after that I couldn't connect to my instance of the server. Any suggestions?

No, because your connection to a SQL Server has absolutely nothing to do with the password for the account SQL Server is running under. The only thing that password affects is whether the SQL Server starts up.
—Michael Hotek, SQL Server Expert

Return to password FAQs

4. Can I change the 'SA' password without my old password?

Use OSQL with the –E option (for authenticated user). If you are an administrator of the OS, you will get in without being prompted for the sa password. Once you are at the OSQL interactive prompt, issue the following command:

EXEC sp_password NULL, 'newpassword', 'username' GO

—Steven Andres, Security Expert Return to password FAQs

5. How can I create a username/password for an SQL Server database?

Here is a great article by Steve Jones that will get you started with SQL users and logins: Beginning SQL Server -- Logins and Users.

—Steven Andres, Security Expert

Return to password FAQs

6. What is the best way to encrypt password fields?

All it requires is for you to utilize an encryption algorithm in your application. Data coming in is encrypted and then sent on to the SQL Server where it is stored. When a validating a user, it passes through your application where the process is reversed. I would very strongly recommend that when you are doing this, that algorithm is locked up and very tightly controlled. In no case should it ever be used to allow a user to decrypt the data and view it in a report or via some other manner. Once encrypted, the data should always be encrypted except during validation processes within your application which never retains the password. There is a third-party tool that can do this for you as well called Encryptionizer.
—Michael Hotek, SQL Server Expert

Return to password FAQs

7. What is the password for 'SA' for an SQL Server 2000 first install?

SQL Server 2000 gets you to assign a password for SA when you install the server, if you have selected mixed mode authentication. There is also a check box you can tick if you really need to insist on a blank SA password – highly not recommended! If you only selected Windows authentication, then converted to mixed mode, you'll need to log on using a Windows login, then assign a password to SA.
—Greg Low, Development Expert

Return to password FAQs

8. Can I recover my password in SQL Server 2000?

There isn't a method to reverse the password stored by SQL Server. If you're using mixed mode, you might be able to use a network sniffer to see the password zoom by on the wire.
—Steven Andres, Security Expert

Return to password FAQs

9. How can I set a password on a database in SQL Server 7.0?

Within the SQL GUI tools, look into setting the "SA" password and also create a regular login for users of the application.
—Steven Andres, Security Expert

Return to password FAQs

10. Can I find out which password I have assigned to the user schema?

There isn't a method to reverse the password stored by SQL Server. If you're using mixed mode, you might be able to use a network sniffer to see the password zoom by on the wire, but your best bet is to change the password and deal with the consequences.
—Steven Andres, Security Expert

Return to password FAQs

Didn't find what you were looking for?

Pose a question to anyone of our SQL Server experts.

You can also browse our SQL Server Topics section for more advice.


This was first published in April 2007

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: