Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Unableto Login from windows\SQL Authentication Both, Admin Locked out Expand / Collapse
Author
Message
Posted Wednesday, May 21, 2014 8:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 1,116, Visits: 847
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?



Post #1573224
Posted Wednesday, May 21, 2014 9:17 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 6:10 AM
Points: 447, Visits: 1,658
Googling got me this; worth a try?

http://thebackroomtech.com/2008/03/26/how-to-change-the-sql-sa-password-from-a-command-prompt/
Post #1573242
Posted Wednesday, May 21, 2014 9:30 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 1,116, Visits: 847
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."
Post #1573245
Posted Wednesday, May 21, 2014 9:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 12,877, Visits: 31,792
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1573246
Posted Wednesday, May 21, 2014 10:03 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 1,116, Visits: 847
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?
Post #1573252
Posted Wednesday, May 21, 2014 10:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 12,877, Visits: 31,792
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1573260
Posted Wednesday, May 21, 2014 10:41 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 1,116, Visits: 847
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?
Post #1573261
Posted Wednesday, May 21, 2014 10:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 12,877, Visits: 31,792
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son


  Post Attachments 
Image3.png (23 views, 66.51 KB)
Post #1573268
Posted Wednesday, May 21, 2014 12:31 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 1,116, Visits: 847
Okay Thanks a lot. I will try that soon and let u know.
Post #1573293
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse