1

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?

  • 1
    Can't you create another account for accessing it? Or what actually is your problem? – James Z Oct 18 '15 at 16:41
  • The software installer has setup the sql 2005 database. While testing the database I have accidentally enter incorrect data, to rectify these entries I wanted to access the database with a client. But as the database is setup by the software installer I am unable to access the database without the sa password. I could reset the sa password using steps provided in the link wikihow.com/Reset-SA-Password-in-Sql-Server , but this will stop the software from making new entries in the database as the password would be manually reset and different from the one setup during installation. – singhramit Oct 18 '15 at 17:17
  • 1
    If you're able to change the sa password, you should also be able to create new logins. – James Z Oct 18 '15 at 17:29
  • Check the user accounts, the software should have created its own account to use which is not the SA, -- if your software requires THE SA account (not just a SA account) then something seems a little off to me – Ste Bov Oct 19 '15 at 9:33
  • I am testing a software which requires sql 2005 for storing the data. your software and the SQL server version - both needs an upgrade.. SQL server next year will be out of support. So ask your software vendor to have the software support the latest versions of SQL server! – Kin Shah Oct 19 '15 at 12:27
1

With SQL Server 2005, you have at least a couple of options

  1. run sqlprov.exe as local admin which allows you to add domain accounts to the sysadmin role

  2. 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 'MyDatabase\MyNTUserName', 'sysadmin';). You can also create a new SQL Server login and add it to the sysadmin role from the same SQLCMD prompt.

1

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 Server\90\Tools\Binn\VSShell\Common7\IDE\Ssms.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:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.exe" using psexec. For example:

psexec -i -c "C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy