Skip to main content

Hello everyone,,

I am working on setting up a connection between my SQL Server database and a C# application using the CData ADO.NET provider for SQL Server but I am running into some authentication issues. I would like to use Windows Authentication but it doesn’t seem to be working as expected: I have double checked the connection string settings and ensured that "Integrated Security" is set to true. However,,, I keep getting an error related to login credentials.

Has anyone here dealt with similar authentication issues using CData’s ADO.NET provider: ?? Also, if there’s a workaround to ensure the connection uses the current user’s Windows credentials,,, I would really appreciate any guidance. I have already gone through this thread https://community.cdata.com/cdata-sync-47/cdata-sync-error-when-creating-a-source-connection-to-sql-server-rpa/ but couldn’t get enough solution.

Thanks in advance for your help, any suggestions or resources would be a huge help as I troubleshoot this.

With Regards,

Marcelo Salas

Hi @marcellosalas,

 

Can you please let us know the error that is getting thrown and the connection string you are utilizing? Additionally, in case you have missed this section, in our documentation we state that in order to enable the driver to obtain login credentials automatically from the identity of the windows user running the process, set the following:

  • AuthScheme: Set this to NTLM.
  • IntegratedSecurity: Set this to true.

Kindly follow this link for more information: Windows Authentication

With that being said, the necessary connection properties you will need to connect to your SQL Server using Windows Authentication are:

  • AuthScheme=NTLM
  • IntegratedSecurity=true
  • Server=localhost
  • Database=<your database name>

In case you stumble upon the following error: System.Data.CData.SQL.SQLException: 'o500] The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. System error: System error: Error verifying Server certificate: 800B0109 A certification chain processed correctly but terminated in a root certificate not trusted by the trust provider.'

This error indicates that while the certificate chain is valid, the root certificate authority (CA) isn’t trusted by the system.

Could you please check that the root CA certificate is trusted on your system? If the root certificate is missing or not included in the trusted store, it can cause this issue. This was not an issue in the previous version, but due to security updates, the root CA now needs to be explicitly trusted to establish a secure connection.

If necessary, you may need to import the root CA into your trust store or update your system’s certificate authorities to resolve the issue.

The easiest way to solve this issue, is to set the SSLServerCert connection property to * which will have the driver not validate the certificate (but this is not recommended for security reasons).

Kindly let me know how it goes.

Virtual Event: Data Virtualization, Reimagined Ma”arch 14, 2024 style=
Terms and ConditionsCookie settings