Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
dallas13
dallas13
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1193 Visits: 1133
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?
Beatrix Kiddo
Beatrix Kiddo
SSC Eights!
SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)

Group: General Forum Members
Points: 946 Visits: 3542
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
dallas13
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1193 Visits: 1133
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
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14955 Visits: 38949
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
dallas13
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1193 Visits: 1133
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
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14955 Visits: 38949
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
dallas13
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1193 Visits: 1133
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
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14955 Visits: 38949
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!

Attachments
Image3.png (32 views, 66.00 KB)
dallas13
dallas13
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1193 Visits: 1133
Okay Thanks a lot. I will try that soon and let u know.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search