Client app send user name, SQL Server 2019 converts that to Windows authentication and throws error.

Kirubakara Senthil Kumar S 191 Reputation points
2021-04-01T18:48:46.52+00:00

We have a desktop application, which connects to SQL server 2019 hosted in Azure using a generic user name and password. This connection works perfectly when the application is executed from Windows 10.

But the same application is not working when executed from Windows 7. In SQL error logs we noticed, It ignores the user name provided, instead it picks up the user who executes this application (Windows authentication) and validates their access to the database. In our case users don't have access to the DB. Hence it is failing.

Please clarify, why it ignores the user name provided and why it expects the Windows authentication here?

Error from SQL Error log: 04/01/2021 14:16:31,MSSQL$SQLPRDXXXX ,Audit Failure,Login failed for user 'NMPC\abcde'. Reason: Token-based server access validation failed with an infrastructure error. Login lacks Connect SQL permission. [CLIENT: IP address],(4),18456,NMPC\abcde,DB SERVER Name.

Windows
Windows
A family of Microsoft operating systems that run across personal computers, tablets, laptops, phones, internet of things devices, self-contained mixed reality headsets, large collaboration screens, and other devices.
4,724 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,654 questions
0 comments
Accepted answer
  1. Kirubakara Senthil Kumar S 191 Reputation points
    2021-04-08T18:26:27.947+00:00

    We are able to resolve the issue after removing the text Integrated Security="" from the UDL file.

    Even if you make this parameter value to false or empty, it is still picking that as True from Win 7. After removing this text completely from the UDL file. We are able to connect.

    Thanks All !!!! for your inputs and support

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2021-04-01T19:01:08.62+00:00

    Please post your connection string. It sounds like you have "Trusted_Connection=true".

    Please see:
    https://www.connectionstrings.com/azure-sql-database/

  2. Erland Sommarskog 100.8K Reputation points MVP
    2021-04-01T21:46:52.057+00:00

    Try UID instead of User Id. Did you leave out the password, or don't you have any. Use PWD for the password.

  3. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2021-04-02T03:26:05.103+00:00

    Hi KirubakaraSenthilKumarS-2261,

    As Erland mentioned, please remember to add parameter “password” for user in the connection string. Please refer to Microsoft OLE DB Provider for SQL Server connection string which might help.
    In addition, the Microsoft OLE DB Provider for SQL Server (SQLOLEDB) remains deprecated and it is not recommended to use it for new development work. It is recommended to use the new Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) instead.
    If you have any question, please feel free to let us know.

    Best Regards,
    Amelia

  4. Kirubakara Senthil Kumar S 191 Reputation points
    2021-04-07T15:59:19.883+00:00

    Hello @Erland Sommarskog ,

    We have tried all the options, still we have the issue open..

    UDL file just has a User ID. Password is stored in a separate .Dat file as encrypted

    UDL connection is successful. But when using this in the app, again it tries to connect using Windows Authentication.

    One difference we noticed is Win 7 is using SSL 2.0 and Win 10 / Win 2019 server are using TLS 1.2.