Authentication of a AD user in SQL Management Studio

Question

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

Solution

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.

Comments

[Admin]

If you want AD authentication why not just add the SQL server to AD? But as for the error you are seeing i would check DNS to make sure it can resolve your AD name (short and FQDN)

[Admin]

My SQL-server is added to the domain. The DNS-server of the SQL-server is pointing to the domain-nameservers so that is not the issue.