question

KirubakaraSenthilKumarS-2261 avatar image
0 Votes"
KirubakaraSenthilKumarS-2261 asked ErlandSommarskog commented

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

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.





sql-server-generalwindows-7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KirubakaraSenthilKumarS-2261 avatar image
0 Votes"
KirubakaraSenthilKumarS-2261 answered ErlandSommarskog commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Great to hear that you found a solution!

Permit me to note that it was difficult for us to give this tip, since the string Integrated Security was not present in the connection string you posted.

To get help in forums it is essential to provide accurate information. We can only work from the data you share with us.

1 Vote 1 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered Viorel-1 edited

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

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

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Desktop application is a Vendor product. It uses UDL file

Content of the UDL file:

[oledb]; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Persist Security Info=False;User ID=generic_user;Initial Catalog=databasename;Data Source=Servername

0 Votes 0 ·
Viorel-1 avatar image Viorel-1 KirubakaraSenthilKumarS-2261 ·

Did you compare the contents of UDL files from Windows 10 and Windows 7 machines?

0 Votes 0 ·

Yes, UDL file shared by Vendor is same for both Windows 10 and Win 7. There were some driver compatibility issues, which we sorted already.


0 Votes 0 ·
Show more comments
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered KirubakaraSenthilKumarS-2261 commented

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


· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Erland for your feedback. There was no password errors.

It works perfectly in two scenarios

  1. When the client Machine is Window 10.

  2. Incase of Window 7, Individual users have to be added to the database.

But my question here is why an individual user have to be added??

Why SQL server 2019 is not accepting the user name and password when coming from Win 7??

0 Votes 0 ·
ErlandSommarskog avatar image ErlandSommarskog KirubakaraSenthilKumarS-2261 ·

So did you try to change the connection string as I suggested? I checked the documentation for SQLOLEDB, and it does not seem to accept User Id for the connection string. (And this may have changed with Windows 10. The very old provider SQLOLEDB ships with the OS.)

I guess that when you don't specify the user name in a way the provider recognizes, it defaults to integrated security. That should answer your question.

0 Votes 0 ·

Thanks a lot.!!. This makes complete Sense. Will test this and confirm back in a day or two!!

0 Votes 0 ·
Show more comments
AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered ErlandSommarskog commented

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


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Amelia. We have updated provider, still the issue exist.

It works perfectly in two scenarios

  1. The client Machine is Window 10.

  2. Incase of Window 7, Individual users have to be added to the database.

But my question here is why an individual user have to be added??

Why SQL server 2019 is not accepting the user name and password when coming from Win 7??


0 Votes 0 ·
ErlandSommarskog avatar image ErlandSommarskog KirubakaraSenthilKumarS-2261 ·

Thanks Amelia. We have updated provider, still the issue exist.

What exactly does that mean? Did you install MSOLEDBSQL and updated the connection string to reflect this?

0 Votes 0 ·
KirubakaraSenthilKumarS-2261 avatar image
0 Votes"
KirubakaraSenthilKumarS-2261 answered ErlandSommarskog commented

Hello @ErlandSommarskog,

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

So what does the UDL file look like now?

Have you verified that the application reads the file you expect? What if you change the server name to something bogus? Does it still fail with a login error? It should be hanging for about 15 seconds until you get a different error message.

0 Votes 0 ·