The challenge ... get sa access to an unknown SQL server express 2008 R2 database using SQL server authentication without installing SQL server management studio when database was setup with windows authentication.
I needed a database on the remote server, and it already had a SQL server express database but i didn't know anything about it including username/passwords. I could log on from my machine using SSMS using windows authentication, but did not have enough permissions to create a database. I figured i needed an sa login (both for my SMSS connection, and for using the database later from software). There's lots of information out there for doing this with SMSS installed locally on the machine, but i didn't want to install SMSS because it's just so big (500MB) and takes a long time and i have a wifi connection to the remote server and i was up for a challenge of how to do it without SMSS.
First, we need to reset the sa password. (Actually, SQL server by default installs without sa enabled and you would normally set a strong password in SMSS and enable the account)
You need to switch SQL server express to run in single user mode i.e. the -m switch.
Start SQL server Configuration Manager (Start > Programs > Microsoft SQL Server 2008 > Configuration Tools), and right click on the Sql server process and select properties. On the "advanced" tab, select startup parameters and you see "-dc:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\master.mdf;-ec:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Log\ERRORLOG;-lc:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\mastlog.ldf". Add ";-m" to the end of this and restart service.
You can run MS SQL Server commands with osql e.g. from command prompt run
osql -E -q "SELECT * FROM sysdatabases"
List your database instances with
osql -L
then run commands on your chosen instance.
Set the sa password to something new...
C:\Users\Administrator>osql -S instance -E
1> sp_password NULL,'yourPassword','sa'
2> go
and enable the sa user
C:\Users\Administrator>osql -E -q "ALTER LOGIN sa ENABLE"
1> go
When i tried to login with SQL Server authentication, i got error 18456
Checking event viewer on the server, it says SQL server is setup for windows authentication.
We need to enable Mixed mode authentication. This is easy enough by changing registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQLServer\LoginMode = 2
Now i can login with sa using SQL server authentication
