locked
ODBC connection to SQL server (server 2016 to SQL 2014) RRS feed

  • Question

  • I am unable to make the ODBC connection, and it appears to be a creds issue;

    I make the connection with my domain admin account and for login "With Windows login ID..."

    And the WORKS

    but I change to the "With SQL sever auth ...."

    And with the exact same use and password, it now FAILS.

    I need this to work because I have a service account defined in SQL, and it has the same error.

    Mixed mode is TURNED 'ON' on the SQL server

    I have installed the latest patches, and SQL client on the ODBC server

    The exact error is

    Connection failed:

    SQLState: '28000'

    SQL Sever Error: 18456

    [Microsoft][ODBC SQL Sever Driver][SQL Server] Login failed for user

    supersecret'correct'user


    BlankMonkey

    Monday, January 22, 2018 4:12 PM

All replies

  • Is your service account not a domain account?

    A Fan of SSIS, SSRS and SSAS

    Monday, January 22, 2018 4:28 PM
  • but I change to the "With SQL sever auth ...."

    And with the exact same use and password, it now FAILS.

    Hello,

    What does this mean, do you mean change to SQL authentication and use your Windows login name and password? That don't work, of course, a SQL login has nothing to do with Windows credentials. You have to create a SQL login and grant permissions for it: CREATE LOGIN (Transact-SQL)

    Is SQL Server running in "Mixed mode" at all = Windows and SQL Authentication? If not: Change Server Authentication Mode


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, January 22, 2018 5:00 PM
  • It is.

    And it is defined on the SQL server, same as my domain admin account


    BlankMonkey

    Monday, January 22, 2018 8:22 PM
  • Ahh yes, I forgot to add that :)
                  Yes, the windows user accounts ARE defined on the SQL server explicitly.
                  On the SQL server under security, my user account is included with the domain\user as an accepted user, as is the domain admin group.

                  The ODBC connection is for trouble shooting, to confirm I can connect to the DB with the account, before I had this off to a developer.


                  The service account and my account are the same, for problem recreation.  But I can logon as my own user account to validate the connection settings with Windows pass through, while the service account can't.

    BlankMonkey

    Monday, January 22, 2018 8:23 PM
  • If the service account is the domain account, you have to use "With Windows NT ..." as your Login ID.

    A Fan of SSIS, SSRS and SSAS

    Monday, January 22, 2018 8:43 PM
  • Yes, the windows user accounts ARE defined on the SQL server explicitly.

    It's confusing what you are saying. As Windows or as SQL login? When it's a Windows login, then you can't pass the Windows in parameter userid/password, it can only be used with SQL login credentials; you have to use the option "With Windows login".

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Teige Gao Wednesday, January 31, 2018 2:15 AM
    Tuesday, January 23, 2018 4:53 AM