Solved

Problems connecting to MS SQL database

Posted on 2013-01-21
16
197 Views
Last Modified: 2013-01-30
Got a problem with MS SQL login and I will need help to rectify it.

My customer's domain controller crashed (SBS 2003) and according to their IT guy there was no way to recover any data of the SBS 2003

So he started clean with a new domain controller.

I do development for them and have a MS SQL database on a Win 2008 R2 server that was part of the old domain.

Now that the new domain controller is in place and the Win 2008 R2 is joined to the domain I can't log in to the MS SQL database.

I know what the old username and password are but I'm not able to log in.

Can someone help me with this? The SQL server version is 2008 R2
0
Comment
Question by:Gerhardpet
  • 10
  • 4
  • 2
16 Comments
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 38804114
You can start the SQL Server in single user mode (sqlservr -m) from the Binn folder of SQL Server. When started this way, you can add your new account to the SQL Server sysadmin group.

But a caveat, if you have changed domains and this SQL Server is not a part of the new domain, it may cause you problems because the machine membership in the domain will determine whether that account can be recognized.

There are Microsoft articles about starting your SQL Server in single user mode.
0
 
LVL 12

Expert Comment

by:NormanMaina
ID: 38804277
You have to realise that the old domain account and the new domain account are different even though they have the same names - they have different security entities.

Two things need to be done:
1.Map sql account to new domain account.
Login to the windows server where sql server is installed and type run>services.msc
In the SQL Server Service,double click to open properties and then go to the Log On tab.
Enter the domain account running sql.

2.Add The SQL LogIN
You have to login to SQL Server Management studio using the sa account or using the local server administrator account(sql sever box)
Then In the security tab,add the new domain account you want to use.
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 38805714
@dbaduck
When I try it start SQL in single user mode as per your suggestion I get the following
error
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 1

Author Comment

by:Gerhardpet
ID: 38805722
btw...the SQL server is part of the new domain already. Also I have full domain admin rights on all servers
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 38805735
What account is the SQL Server service set to startup as?  Check in SQL Configuration Manager.
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 38805779
Here is the screenshot of the services
account config
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 38805813
So the first thing I would do is to edit the SQL Server Service and change it to Network Service, and then change it back to SYSTEM.  The SIDs may have become messed up on the change.
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 38805866
Ok did that but still get the same results with sqlservr -m
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 38806037
Then you can try to see if it will start in minimal mode.

sqlservr -f
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 38806696
Ok now I also created a local admin user on the server as per NormanMaina and this is what I get when I try to log in with the local admin
sql error
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 38806753
Get the same error with sqlservr -f as before
0
 
LVL 12

Expert Comment

by:NormanMaina
ID: 38807252
what you need to do is remove the sql server machine from the domain and then rejoin it to the domain -if thats not been done already.
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 38807275
Yes that has been done already. It is part of the new domain
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 38812105
I have tried other articles I found on this but so far have not been successful in connecting to the database. My last resort is to call Microsoft support to help with this.

Anyone else got any ideas for me?
0
 
LVL 1

Accepted Solution

by:
Gerhardpet earned 0 total points
ID: 38818259
Problem solved by calling microsoft for help

Here are few articles that may he helpful on this issue

Start SQL Server in Single-User Mode:
http://msdn.microsoft.com/en-us/library/ms188236.aspx 

How to: Start an Instance of SQL Server (sqlservr.exe):
http://msdn.microsoft.com/en-us/library/ms180965(v=sql.105).aspx 

sqlcmd Utility:
http://msdn.microsoft.com/en-us/library/ms162773.aspx 

DROP LOGIN (Transact-SQL):
http://msdn.microsoft.com/en-in/library/ms188012.aspx
0
 
LVL 1

Author Closing Comment

by:Gerhardpet
ID: 38834492
Solved
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
This article explains how to install and use the NTBackup utility that comes with Windows Server.
This tutorial will give a an overview on how to deploy remote agents in Backup Exec 2012 to new servers. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as connecting to a remote Back…
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now