SQL Server Windows Authentication

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
b1dupreeAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jeff PerryConnect With a Mentor Windows AdministratorCommented:
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
 
Jeff PerryWindows AdministratorCommented:
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
 
Justin YeungSenior Systems EngineerCommented:
login to the SQL server with our old domain credential and then open SMSS.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
b1dupreeAuthor Commented:
Tried logging in with a local administrator but Management Studio still had the username and password greyed out.
0
 
Lee IngallsDirector of IT/TS, Quality and FinanceCommented:
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:
1
 
b1dupreeAuthor Commented:
Justin, can't login with old server credentials because the domain name has changed.
0
 
Justin YeungSenior Systems EngineerCommented:
do you have mixed mode setup before? or only windows authentication enabled previously?
0
 
b1dupreeAuthor Commented:
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
 
b1dupreeAuthor Commented:
Justin, I didn't set up the database, so how would I know about mixed mode?
0
 
Justin YeungSenior Systems EngineerCommented:
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
 
Jeff PerryWindows AdministratorCommented:
Can you remove and then re-install management studio?

I did not realize that SSMS could get bound this way.
0
 
b1dupreeAuthor Commented:
I can't see what installing the Management Studio will do.  The authentication is for the database.
0
 
Justin YeungSenior Systems EngineerCommented:
there is nothing to do with the SMSS............
0
 
Lee HowleyCommented:
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
 
b1dupreeAuthor Commented:
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
 
Justin YeungSenior Systems EngineerCommented:
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
 
b1dupreeAuthor Commented:
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
 
Jeff PerryWindows AdministratorCommented:
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
 
Lee HowleyCommented:
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
 
Justin YeungSenior Systems EngineerCommented:
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
 
b1dupreeAuthor Commented:
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
 
Jeff PerryWindows AdministratorCommented:
That was also listed in the Disaster Recovery link suggested.
0
 
Lee HowleyCommented:
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
 
b1dupreeAuthor Commented:
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
 
b1dupreeAuthor Commented:
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
 
Jeff PerryWindows AdministratorCommented:
Glad you got it working.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.