Unableto Login from windows\SQL Authentication Both, Admin Locked out

Question

Post reply

Unableto Login from windows\SQL Authentication Both, Admin Locked out

dallas13

SSCertifiable

Points: 7491
More actions
May 21, 2014 at 8:48 am

#291646

Hello,
Some application installed sql server 2008 Standard Edition on windows 2008 Server. The person who installed that application is left. We don't know what is SA account password. SQL Server is running under "Local system" Account. I am a local domain admin on that Box. But I can not connect to that instance of SQL server due to login failed error. I tried to change SQL Server Service account to "Local Service" AND "Network Service" but still can not connect.
Can someone tell me what to do now?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 1

Googling got me this; worth a try?

http://thebackroomtech.com/2008/03/26/how-to-change-the-sql-sa-password-from-a-command-prompt/

dallas13 SSCertifiable Points: 7491 More actions · Answer 2

Thanks but for that you should be able to connect to SQL server using windows authentication too. And My question is I can not connect using windows authentication.

I already tried "sqlcmd -S."

Lowell SSC Guru Points: 323518 More actions · Answer 3

this is my go-to script for this issue:

Login to the server itself, make sure you have local admin rights on the operating system side of things.

Now simply download and run this script from an administrative command prompt window:

https://gist.github.com/wadewegner/1677788

you are prompted for which instance(blank fro the default instance, otherwise put in the name)

it adds you, the current logged in windows user, as a sysadmin to the SQ Linstance. it stops and starts the service, so mission critical servers should wait until a maintenance window when you can bounce the service, but it's a fast process.

repeat for each SQL instance on the machine you need to take over.

I've used this on nearly every server I have, in a recent takeover; network guys gave me local admin to a box, and i took over the SQL side of things.

the original copy of this script, direct from microsoft was here, but has been moved because they retired the archive. I found a copy by searching for "addselftosqlsysadmin"

http://archive.msdn.microsoft.com/Project/Download/FileDownload.aspx?ProjectName=addselftosqlsysadmin&DownloadId=9198

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dallas13 SSCertifiable Points: 7491 More actions · Answer 4

Hello Sorry but did not work for me, here is what I got,

Adding 'domain\user' to the 'sysadmin' role on SQL Server instance 'SQLEXPRESS'.

Verify the 'MSSQL$SQLEXPRESS' service exists ...

[SC] EnumQueryServicesStatus:OpenService FAILED 1060:

The specified service does not exist as an installed service.

'MSSQL$SQLEXPRESS' service is invalid

'domain\user' was NOT added to the 'sysadmin' role: SQL return code is N/A.

I have SQL Server 2008 Standard Edition and its a named instance. It will still work?

Where do I have to find and replace?

Lowell SSC Guru Points: 323518 More actions · Answer 5

no find and replace required;

check the name of the instance, and also make sure the instance is running in services.

for example, if i go to the services applet in administrative tools, on my dev machine i see the names here:

i have four different instances.

to take over the default MSSQLSERVER, i type MSSQLSERVER

to take over any of the others, i type only the name of the instance, ie "SQL2012", and the script should work.

I don't have a SQLEXPRESS instance, for example, so i'd get the same error as you if i left it blank.

to take over this server, i'd have to run the script four times, once for each of these:

MSSQLSERVER

SQL2005

SQL2008R2

SQL2012

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dallas13 SSCertifiable Points: 7491 More actions · Answer 6

Thanks again But it did not prompted me to specify my instance name.

I dont have any default instance but I have only one named instance. Thats what I can see in Services.msc.

So it does not prompt me to do specify instance name. and gave me this error message. How do I resolve that?

Lowell SSC Guru Points: 323518 More actions · Answer 7

i suspect that's not the exact sameversion then, my apologies. i assumed it was the same.

this is the exact version i am using; does this help?

addselftosqlsysadmin.zip

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dallas13 SSCertifiable Points: 7491 More actions · Answer 8

Okay Thanks a lot. I will try that soon and let u know.