The All famous SA login in SQLSERVER : Can it be enabled differently ?

Question

Hey Guys :D

For my final year project, I'm making what I call - SeeQuaL and it's a one stop interface for accessing 3 of the most famous database types - SQLite, SQL Server and MySql. I'm Pretty sure that I can get things done with the SQLite and MySql. But SQL Server, meh! will surely give me problems.

Now lets take the case that I have SQL Server Express 2005 installed on my system but I don't have Management studio installed. In order to access my databases , I will need to log in with the SQL server authentication mode AKA via the all famous login - SA.

So here is my Question:

How do I enable this account (sa) and set the server authentication to "SQL Server and Windows authentication" mode WITHOUT using management studio??

I have no idea as to how to accomplish this :( So please help me guys ! Is there some table that I need to access thro the windows authentication mode by which I can get this job done ?

Answer 1

First off are you running Windows? If so what version? I'm going to assume your running Windows 7, but from memory the steps are the same for Vista

1. Bite the bullet and download SQL Server 2008 Management Studio Express
2. After you've installed SMSE, Right click on the SMSE link in your start menu and click "Administrator".
3. Connect to your local SQLEXPRESS installation using Windows Authentication.
4. In Object Explorer, Right Click on your SQLEXPRESS instance and select "Properties"
5. Click on Security and select "SQL Server & Windows Authentication mode" and click OK
6. Expand Security > Logins and then right click on your "sa" user, and select properties.
7. Set the "sa" users password to something you'll remember, I suggest using a Passphrase rather than a password.
8. Click OK, and you should be good to go..

You can test by closing and re-opening SMSE without Administrator privileges and try connecting with your SA information.

Answer 2

If you are writing something that pretends to be a replacement for Management Studio, learn what Management Studio does in the first place.

You can trap almost everything Management Studio does using Script button dropdon and selecting "Script Action to New Query Window" at the top of the dialog window after doing the actions you want but just before pressing the Ok button.

Here is the SQL code that is used by Management Studio to do what you are asking for:

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO

Another option is using Profiler. It's included in Developer Edition.

Answer 3

Hi Skun, I think I can give you the answer, because I've just solved it recent days.

To "SQL Server and Windows authentication" mode WITHOUT using management studio, the answer is plain simple except M$ does not want to tell you explicitly.

Expand registry key

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer

there you will see a REG_DWORD value named LoginMode , if 1 it is Windows Authentication, if 2, it is Windows+SQL Authentication. What you need to do is changing it from 1 to 2 and restart SQLEXPRESS services.

One more thing to do, to enable sa and set its password, which is disabled by default.

Right on you SQL express machine, login to you SQLEXPRESS services instance with command

sqlcmd -S localhost\SQLEXPRESS

then exeucte

1> ALTER LOGIN sa ENABLE ;
2> ALTER LOGIN sa WITH PASSWORD = 'mypassword' ;
3> GO

Now you're done.

I learned these from the following URL:

• http://decipherinfosys.wordpress.com/2008/11/14/the-user-is-not-associated-with-a-trusted-sql-server-connection/
• http://msdn.microsoft.com/en-us/library/ms188670.aspx