Sql-server – Authentication of a AD user in SQL Management Studio

active-directoryauthenticationsql server

I'm struggling couple of days with the following issue.

I'm want to let people, that are a member of a AD authentication with their username/password on SQL Management Studio 17.x but I cannot use Windows Authentication, because the computer they are working on is not joined in the domain.

There is a option for "Active Directory – password". I had the issue that my certificate was not trusted. That has been solved, but now I get he message;
OR: Target principal name not found/incorrect
OR: Login failed for user .

My SQL-servers are configured in Mixed-mode.

Hope somebody can help me, because now my remote users, cannot login onto SQL with their own AD credentials.

BTW, I do see allot of solution to use batch-file to launch it, but I'm hoping that there is another way because in that case, the credentials are still plain text on that workstation.

Best regards
Dave

Best Answer

You do not need to have all machines on the same domain for Windows authentication to work.

Go into your Windows credential store. On Windows 10 that's under Control Panel, User accounts, Manage your credentials.

Add a new Windows credential where the network address is hostname:1433 (or whatever port you use), the username is the fully specified DOMAIN\Username, and use the appropriate password.

Then try connecting to MSSQL in Windows authentication mode, and it should work using the credential you just created.

Related Solutions

SQL Server – Can’t Connect Using ‘sa’ Account, What Am I Missing?

Like @SpaceManSpiff said, don't forget to check if Mixed mode is enabled. Someone changed that setting for me and I had the same problem. Here is how to solve it:

Login to the MSSQL Server Management Studio with Windows Authentication.
In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
Under the Server Properties, select a page of "Security".
Select the Server authentication as "SQL Server and Windows Authentication mode" and click Ok.
Restart the SQL Services and then try to login with 'sa' details.

Source: http://forums.eukhost.com/f15/login-failed-user-sa-microsoft-sql-server-error-18456-a-12544/

Sql-server – MS SQL Server 2008 sa account locked out and Windows Authentication login is greyed out

Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out:

Resolution

Start the instance of SQL Server in single-user mode by using either the -m or -f options. Any member of the computer's local Administrators group can then connect to the instance of SQL Server as a member of the sysadmin fixed server role.

To start the SQL with -m (from a console running as administrator):

net stop mssqlserver
cd "c:\program files\Microsoft SQL Server\...\MSSQL\binn"
sqlservr -c -m

Or, if you have a named instance:

net stop mssql$<INSTANCENAME>
cd "c:\program files\Microsoft SQL Server\...\MSSQL\binn"
sqlservr -c -m -s <INSTANCENAME>

There are alternative means as shown in How to: Configure Server Startup Options, but I always preffer start from command prompt for something like this. After you start the instance with -m:

Open with SSMS using Run as Administrator
At the first prompt don't click connect but say cancel
Open a new query window and then connect to the database with that query window
Now add yourself to the SQL sysadmin group

create login [domain\you] from windows;

exec sp_addsrvrolemember 'domain\you', 'sysadmin';

Now shutdown your SQL Server and restart it normally (start back the service). domain\you is now a sysadmin on the server and can administer it. You don't need to run SSMS as administrator.

Best Answer

Related Solutions

SQL Server – Can’t Connect Using ‘sa’ Account, What Am I Missing?

Sql-server – MS SQL Server 2008 sa account locked out and Windows Authentication login is greyed out

Related Topic