(English) SQL Server Issue for OLEDB/JDBC with Force Encryption, Extended Protection and Windows auth.

[:en]

Issue:

SQL Server exposes the settings „Force encryption“ and „Extended Protection“ within the protocols properties in SQL Server Configuration Manager.

Force encryption enforces the use of SSL/TSL for Transport encryption when communicating with the SQL Server

Extended protection is used to prevent a „man in the middle“ attac using an existing connection to the SQL Server

When using BOTH Features oleDB and JDBC will throw an error „Login failed. The Login is from an untrusted Domain and cannot be used with Windows authentication“

Check for relevance:

reproduce the error:

create a file with Suffix „.udl“, open it with „oleDB core Services“, select „Microsoft OLE DB provider for SQL Server“ and enter Connection properties. Test Connection.

Connection string in your apps is like

„Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=database;Data Source=server“

Check for Connections in your database:

Select

s.client_interface_name
from sys.dm_exec_connections c
inner join sys.dm_exec_sessions s
on c.session_id = s.session_id

Check encryption in your database:

SELECT net_transport, auth_scheme, encrypt_option
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

Solution Options:

– Switch off one of the security Features (not recommended!)
– Use the SQL Server Native Driver (The SQL Server Client might have to be installed to allow the use of a Connection string like „Provider=SQLNCLI11;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=database;Data Source=server“)
– Refactor to a more modern data Access strategy like asp.net or entity

 [:]