I am testing a software which requires SQL Server 2005 for storing the data. The SQL Server 2005 database is automatically installed when I installed the software. During the software installation I didn’t encounter any screen to configure the SQL Server 2005 database so I am assuming the configuration is done by the software installer.
I can reset the password for the
sa but theoretically it would stop the application from working. Can anyone push me in a direction by which I can access the database with a client?
With SQL Server 2005, you have at least a couple of options
run sqlprov.exe as local admin which allows you to add domain
accounts to the sysadmin role
stop SQL Server and start in single user mode with the parameter -m
and use SQLCMD or OSQL to add your domain account to the sysadmin
role (EXEC sp_addsrvrolemember ‘MyDatabaseMyNTUserName’,
‘sysadmin’;). You can also create a new SQL Server login and add
it to the sysadmin role from the same SQLCMD prompt.
If you don’t know the password, you can just use psexec to execute a command prompt as the NT SYSTEM AUTHORITY user. Then you can run oSQL commands, or open up SSMS which will let you create an SA user equivalent. This comes up from time to time, especially if you inherit servers.
1-Download PSExec from Microsoft.
2-use oSQL or find path to your SSMS (right click on the shortcut, get path or look for ssms.exe. Default path is:
C:Program Files (x86)Microsoft SQL Server90ToolsBinnVSShellCommon7IDESsms.exe (I think 90 is sql 2005. Not sure, put in your path). I find oSQL easier.
3-Execute PSExec with a command shell with elevated admin privileges and open up SSMS or OSQL from psexec. If you want to open up a new window open up powershell.exe or cmd from
"C:WINDOWSsystem32WindowsPowerShellv1.0powershell.exe” using psexec. For example:
psexec -i -c "C:WINDOWSsystem32WindowsPowerShellv1.0powershell.exe"
Then create a new user and add it as an SA equivalent with something like:
USE [master] GO CREATE LOGIN [NotSA] WITH PASSWORD=N'VeryStrongPW!' DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON GO ALTER SERVER ROLE [sysadmin] ADD MEMBER [NotSA] GO