Solved

SQL Server Windows Authentication

Posted on 2014-02-24
26
2,885 Views
Last Modified: 2014-02-24
I installed a new domain controller and change the domain name and now I cannot log into the SQL Server using Windows Authentication.  (The SQL Server hardware was not changed but I took it off the old domain and put it on the new domain).

The login for the SSMS.exe (Management Studio) shows the NEWDomain/user but it is greyed out so I cannot change it and I cannot log in.

I tried using the command line runas /netonly /user command with both the new and old domain but it did not work
0
Comment
Question by:b1dupree
  • 10
  • 6
  • 6
  • +2
26 Comments
 
LVL 8

Expert Comment

by:Jeff Perry
ID: 39882726
Can you log onto the SQL server with the old domain user account? Hopefully it is still cached and will let you.

It may be possible also to logon as the local administrator and open SSMS as the sa account to add your new domain account and permissions.
0
 
LVL 14

Expert Comment

by:Justin Yeung
ID: 39882750
login to the SQL server with our old domain credential and then open SMSS.
0
 

Author Comment

by:b1dupree
ID: 39882753
Tried logging in with a local administrator but Management Studio still had the username and password greyed out.
0
 
LVL 7

Expert Comment

by:Lee Ingalls
ID: 39882757
I ran across this method some years ago and used this workaround for Authenticating to MS SQL Server on a Different Domain Using Windows Authentication

When trying to connect to an MSSQL server on another domain (of which you are not a member), you'll probably run into the issue where selecting the Windows Authentication option in the SQL Server connection dialog grays out the username and password. Here are two workarounds:

Scenario
SQL Server: sqlserver.mydomain.com
Domain: MYDOMAIN
MYDOMAIN Username: joe.user

A) Create Saved Credentials
Go to the Control Panel --> User Accounts --> Manage Your Credentials (in left-hand pane) --> Add a Windows Credential

Address: sqlserver.mydomain.com
User Name: MYDOMAIN\joe.user
Password: (password for joe.user on MYDOMAIN)
Click OK

Now, when you fire up SQL Server Management Studio, enter your server name (sqlserver.mydomain.com) and select Windows Authentication. THE USERNAME WILL BE GRAYED OUT but that's fine. The connection will authenticate properly anyhow. Go ahead and connect.

B) RunAs Command Line (requires user interaction for password)
Open a command line and run this:
runas /netonly /user:MYDOMAIN\joe.user "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe -S sqlserver.mydomain.com -E"
(You will need to alter the SQL Management Studio exe path as is applicable to your PC)
When prompted on the command line, enter the password for joe.user.
SQL Management Studio should load and automatically connect to the SQL server.

If you are unable to connect with either of these scenarios, check the firewall settings on the remote system.

References:
0
 

Author Comment

by:b1dupree
ID: 39882763
Justin, can't login with old server credentials because the domain name has changed.
0
 
LVL 14

Expert Comment

by:Justin Yeung
ID: 39882774
do you have mixed mode setup before? or only windows authentication enabled previously?
0
 

Author Comment

by:b1dupree
ID: 39882782
Lee, I'm logging directly into the SQL Server (called "SQL") . I am not having a problem logging into the server where SQL is installed, My only problem is logging into the Management Studio (because the domain name is different than before).

 When I setup the credentials in Windows Server, setting them up for the old domain name for the old or new domain?  I tried both but neither works.
0
 

Author Comment

by:b1dupree
ID: 39882788
Justin, I didn't set up the database, so how would I know about mixed mode?
0
 
LVL 14

Expert Comment

by:Justin Yeung
ID: 39882793
first when you change the domain of the machine, the windows authentication within SQL will remain, it is not automatically flipped with server domain change.

if you mixed mode is not turned on by default, you will need to change your server back on to the old domain in order to logon.

hope that you still have your old Domain controller remain.
0
 
LVL 8

Expert Comment

by:Jeff Perry
ID: 39882927
Can you remove and then re-install management studio?

I did not realize that SSMS could get bound this way.
0
 

Author Comment

by:b1dupree
ID: 39882943
I can't see what installing the Management Studio will do.  The authentication is for the database.
0
 
LVL 14

Expert Comment

by:Justin Yeung
ID: 39882960
there is nothing to do with the SMSS............
0
 
LVL 8

Accepted Solution

by:
Jeff Perry earned 500 total points
ID: 39883071
Hopefully you can gain access by logging in via Disaster Recovery.

http://blogs.msdn.com/b/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx

If you are stuck in windows authentication mode follow this guide to change it to mixed mode.

http://msdn.microsoft.com/en-us/library/ms188670%28v=sql.105%29.aspx
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Expert Comment

by:Lee Howley
ID: 39883101
It sounds like the machine that has SQL Server Management Studio isn't joined to the same Domain as the the actual SQL server.

If I read though everything correctly, you moved the server to the new domain, but not the machine with management studio. You can't choose a different domain once you're logged in to your windows account (at least in management studio) to the best of my knowledge. Perhaps I misread though.
0
 

Author Comment

by:b1dupree
ID: 39883158
I moved the SERVER (where SQL is installed) to a new domain.  I can log into the SERVER (on the new domain) but not into the Management Studio.  The Management Studio login is greyed out.  The user in the login is the logged in user for the new domain but the password field is greyed out and if I press the CONNECT button it says I have the wrong password.

Would re-installing the Managment Studio allow me to access the database?
0
 
LVL 14

Expert Comment

by:Justin Yeung
ID: 39883249
it is always grey out when you select windows authentication since it is passing your windows credential.

Your SQL user account has not been configured with your new domain user object yet.

another word, you SQL are still contain OLDDOmain\Username within the ACL.

you will have to login with your OLD domain credential setup SQL authentication (if it hasn't been set to mixed mode) then change to new domain, add new domain user by sign in with SQL account.

do you still have your OLD domain domain controller available?
0
 

Author Comment

by:b1dupree
ID: 39883276
Yes, I do but wanted to avoid going onsite.

Do you think that reinstalling the Management Studio will allow me to reconnect (without connecting back to the old domain controller)?
0
 
LVL 8

Expert Comment

by:Jeff Perry
ID: 39883280
I doubt re-installing ssms will help that was a misunderstanding on my part when you said you could log into the SQL server.
0
 

Expert Comment

by:Lee Howley
ID: 39883291
You might be able to log in with the old domain credentials. I think you can specify credentials for certain servers. Not sure this will work in the specific scenario though.

start/control panel/user accounts/credential Manager
0
 
LVL 14

Expert Comment

by:Justin Yeung
ID: 39883310
it is probably not going to work with the credential manager, since you will have to logon with that credential, it will say no logon server found at some point.

or if the cache remain, it should allow you to login with the old domain credential. but I believe he already tried it.

so only option is old domain, sign in, enable SQL authentication, change to new domain, sign in with SQL SA account, then add new domain windows user to SQL and or remove the old domain user from SQL after it.
0
 

Author Comment

by:b1dupree
ID: 39883393
I tried credential manager using server.domainname.local
I heard that you can use the port number or 1433 as in server.domainname.local:1433 but that didn't work either.


I saw this:

http://zarez.net/?p=105

I just couldn't figure this part out:

This will put the SQL into Single User Mode, and SQL Server will only accept one connection.

Connect with SQL Server Management Studio and add login with sysadmin privilege, or use sqlcmd to connect to your server with the -E trusted connection option. You will be able to connect to the SQL Server as sysadmin if you are a local administrator.

Create your login and add it to the sysadmin role.

USE master
GO
CREATE LOGIN [domain\username] FROM WINDOWS WITH DEFAULT_DATABASE=[Master]
GO
EXEC sp_addsrvrolemember @loginame=N'domain\username', @rolename=N'sysadmin'
GO

Stop the SQL service, remove the -m parameter and restart the service. You should now be able to connect to the SQL Server normally.
0
 
LVL 8

Expert Comment

by:Jeff Perry
ID: 39883398
That was also listed in the Disaster Recovery link suggested.
0
 

Expert Comment

by:Lee Howley
ID: 39883421
I think it is telling you to login as sysadmin. In the scenario above, if you are a local admin, you can login to sysadmin. I am assuming the password would be the same as the admin account you are on.
0
 

Author Comment

by:b1dupree
ID: 39883585
I tried this but the login is stilled greyed out after adding the user is sqlcmd although it shows the LOCALCOMPUTER/USERNAME as opposed to the DOMAIN/USERNAME after I restart the SQL Service:

1. Open the Configuration Manager tool from the "SQL Server 2005| Configuration" menu

2. Stop the SQL Server Instance you need to recover

3. Navigate to the “Advanced” tab, and in the Properties text box add “;–m” to the end of the list in the “Startup parameters” option

4. Click the “OK” button and restart the SQL Server Instance

 

NOTE: make sure there is no space between “;” and “-m”, the registry parameter parser is sensitive to such typos. You should see an entry in the SQL Server ERRORLOG file that says “SQL Server started in single-user mode.”

 

5. After the SQL Server Instance starts in single-user mode, the Windows Administrator account is able to connect to SQL Server using the sqlcmd utility using Windows authentication. You can use Transact-SQL commands such as "sp_addsrvrolemember" to add an existing login (or a newly created one) to the sysadmin server role.

The following example adds the account "Buck" in the "CONTOSO" domain to the SQL Server "sysadmin" role:

 

EXEC sp_addsrvrolemember 'CONTOSO\Buck', 'sysadmin';

GO

6. Once the sysadmin access has been recovered, remove the “;-m” from the startup parameters using the Configuration Manager and restart the SQL Server Instance
0
 

Author Closing Comment

by:b1dupree
ID: 39883690
I was able to log into SQL Management Studio.  It took a while but I used this article as the basis:

http://blogs.msdn.com/b/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx

Here are the step-by-step instructions:

1.      Log in as the Server LOCAL ADMINISTRATOR as in LOCALCOMPUTER/ADMINISTRATOR
2.      Open the Configuration Manager tool from the "SQL Server 2005| Configuration" menu
3.      Stop the SQL Server Instance you need to recover, Stop all SQL Services
4.      Right click on the SQL Server Service and go to Properties
5.      Navigate to the “Advanced” tab, and in the Properties text box add “;–m” to the end of the list in the “Startup parameters” option
6.      Click the “OK” button and restart the SQL Server Instance
7.      Open an Administrator Command Prompt and type “SQLCMD”
8.      Enter two lines as follows:

EXEC sp_addsrvrolemember 'NEWDOMAIN\Administrator', 'sysadmin';

GO
9.      Remove the “;-m” as shown above in Properties in the Configuration Manager, restart the service
10.      Log off the LOCAL ADMINISTRATOR account
11.      Log into the Domain Administrator account DOMAIN/Administrator
12.      Open up the SQL Management Studio


Add SQL Authentication
In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
On the Security page, under Server authentication, select the new server authentication mode, and then click OK.

Add a Password:
Login into SQL server using Windows Authentication.
In Object Explorer, open Security folder, open Logins folder. Right click on sa account and go to Properties.
Type a new SQL sa password, and confirm it. Click OK to finish.

Thanks.
0
 
LVL 8

Expert Comment

by:Jeff Perry
ID: 39883758
Glad you got it working.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 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

12 Experts available now in Live!

Get 1:1 Help Now