Home / server / Questions / 879200
In Process
Dave Greebe
Asked: 2017-10-19 20:20:17 +0800 CST

Authentication of a AD user in SQL Management Studio

  • 772

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

active-directory

3 Answers

  1. 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.

    • 1

  2. An update to this question for clarity. You CAN authenticate to a SQL server using SSMS 17 via AD if that SQL server is a Azure SQL server, and the Azure SQL server is joined to Azure AD. The "Active Directory - password" option is to connect to Azure SQL Servers only at this time.

    • 0

  3. You cannot connect to the server and authenticate with Windows credentials if the client systems are not connected to the domain.

    "Windows Authentication" with SQL Server effectively means "pass through authentication backed by domain trust." That means that you can only log in to a Windows account that you're already authenticated and logged in with.

    You can see this when you open up SQL Server Management Studio. There's a drop down that lets you pick either SQL Authentication, which requires a username and a password, or Windows Authenication, which does not allow you to enter a different username or password. In order to log in to SSMS with different Windows credentials, you have to run the SSMS application itself as a different Windows user with Windows' "Run as..." feature.

    You're not going to be able to do that here because you can't authenticate as a Windows user with a domain account while not on a computer on the domain. There's no trust between those workstations and the domain, so you're not going to be able to establish the session required to do the trusted authentication.

    You may try runas.exe with the /netonly switch, but I cannot confirm that this will work:

    runas /netonly /user:domain\username ssms.exe
    • -1