Deploying our development machines from virtual machine templates saves us a lot of time, however every now and then we hit frustrating little speed bumps. Here is the latest – SQL Server 2008 / SQL Server 2008 Express not having an administrator account enabled.

To fix this problem you must first stop the SQL Server 2008 instance, and start it in “single user mode” (you need to be local administrator for this to work).

1. Stop the SQL Server instance in Service Manager
2. Open cmd prompt
3. browse to appropriate SQL Server installation: e.g. cd\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn
4. Start SQL Server in single user mode using the “-m” switch:
   For a default instance: sqlservr.exe -m
   For SQLEXPRESS/named instance: sqlservr.exe -m -sSQLEXPRESS
   
5. You can now connect to the SQL Server instance via SQL Server Management Console and create your administrative user, or use SQLCMD from the command prompt e.g.:

   C:\> SQLCMD -S .\SQLEXPRESS -E
   1> CREATE LOGIN [YOURDOMAIN\justin] FROM WINDOWS
   2> go
   1> exec sp_addsrvrolemember @loginame='YOURDOMAIN\justin', @rolename='sysadmin'
   2> go
   

6. Stop the server (Ctrl+C)
7. Start the service again from Service Manager

Login failed for user ‘username’. The user is not associated with a trusted SQL Server connection.

This error occurs when you attempt to login to SQL Server using SQL Server Authentication mode (username + password) when the SQL Server instance is only configured for Windows Authentication.

To fix the issue:

1. Enable SQL Server Authentication
2. Enable the “sa” user, and configure a password for it
3. Restart the SQL Server Instance

Continue reading SQL Server 2005: Error: 18452 Login failed for user ‘username’. The user is not associated with a trusted SQL Server connection. →