There are two modes for authenticating user connections in SQL Server and MSDE:
- SQL Server Authentication
- Windows Authentication
The server can be configured to accept Windows Authentication only or Both authentication modes. If the server is configured to accept Windows Authentication only, clients connecting with a SQL Server account may get this error message. Also, if you are connecting by specifying Windows Authentication and your Windows account context does not have access to the server, you may get this error message.
Changing SQL Server and MSDE authentication mode
By default, the value of the LoginMode Windows registry subkey is set to 1 for Windows Authentication and 2 for Mixed Mode Authentication. To enable Mixed Mode authentication after installation, you must change this value to 2.
To change the value of LoginMode to 2, follow these steps:
- In Control Panel, open the Services tool to stop MSSQLSERVER and all other related services (such as SQLSERVERAgent)
- To open Registry Editor, click Start, click Run, type regedt32, and then click OK.
- Locate either of the following subkeys (depending on whether you installed SQL Server/MSDE as a default instance or as a named instance):
Default Instance:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer
-or-
Named Instance:HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer
- In the right pane, double-click the LoginMode subkey.
- In the DWORD Editor dialog box, set the value of this subkey to 2, make sure that the Hex option is selected, and then click OK.
- Restart the MSSQLSERVER and the SQLSERVERAgent services for this change to take effect.
WARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system.
For further information, refer to http://support.microsoft.com/kb/319930/EN-US/
Beekeeper Studio Is A Free & Open Source SQL Server GUI
I’ve upgraded from SQL Server and it was a smooth change — Beekeeper Studio enhanced my own workstream, offering a cleaner experience and better performance, as well as flexibility. - Aman Bhattarai
Beekeeper Studio is a fast, intuitive and easy to use SQL Server client. It supports loads of other databases too, and works great on Windows, Mac and Linux.
What SQL Server Users Say About Beekeeper Studio
So far the most comprehensive DB admin GUI out there. The only reason I haven’t given 5 stars is I cannot claim to have used it exhaustively only having discovered it two weeks ago. But so far, mySQL, MSSQL and Postgres in one tool is simply incredible.
Planning to purchase once pay day hits.
I had been using the free version of Beekeeper studio for a while, but when I needed to work with some MongoDB databases I upgraded to the paid version. And honestly, I was up and running in no time. Beekeeper is slick, fast, and it just works. Very intuitive, very easy to use. I work with MongoDB, Postgresql, SQL Server, and other databases in my job, and having one tool that can talk to them all is just wonderful.
Thank you, Matthew and team!