1

I'm able to login to SQL Server Management Studio using the SQL authentication mode (username: sa) but am unsuccessful with using Active Directory Password Authentication. I have gone on the SQL Server Config Manager and added a domain user and password on the 'log on' section of the SQL Server Properties. When I try to login to SQL using the domain user, I get an error saying:

A connection was successfully established with the server, but then an error occurred during the login process. The certificate chain was issued by an authority that is not trusted.

When logged in with the sa user, I notice that my domain user is in the Logins folder along with the sa account. My troubleshooting so far consists of checking the properties of the domain user file in the Logins folder. In the properties, the box that enables the domain account to login to SQL is checked. Also, the SQL server and AD server are on the domain.

Could I be missing a configuration somewhere? Do I need to assign permission to the domain account for it to be able to access the SQL server and management studio?

This is the dialog box with the error after I try to login using a domain account:

image

  • Is the server set up for mixed authentication? You can find this in management studio by looking at the server properties. Also, to answer your question in re: granting the Windows login permission to connect, that's a true statement. You need to grant permission for any login to connect; Windows isn't special in this respect. – Ben Thul Sep 15 '16 at 20:06
  • Hello Ben, the server is set up for mixed authentication. In the properties, the box next to "SQL Server and Windows Authentication Mode" is checked. Where do I grant permission to the domain user? Would that be a configuration in the Domain Controller or the SQL server? Thanks for your time. – dgo1970 Sep 15 '16 at 21:03
0

Here's the link for some of the roles that you can grant Server-Level Roles and Database-level Roles. Searching online, I've found this resource with screenshots for you. where you can grant access to domain user on the third screenshots SQL Server: Setting Domain User Permissions. Although you haven't specified the version, but the steps are similar on various versions of MS SQL

  • Hello Lex, I followed the instructions from the helpful link you provided. Unfortunately, I am now getting a "Login failed for user, error: 18456 state 1" error. – dgo1970 Sep 16 '16 at 20:02
  • Never mind, it works. It turns out that I need to be logged in to the SQL VM with the actual domain account that will access the SQL server. I am new to SQL and didn't know this, thank you for your help. – dgo1970 Sep 16 '16 at 22:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.