Linked Server Selection Query Fails with "MS DTC has stopped this transaction"
Hi everyone, I’m currently setting up a Linked Server from our head office (server A) SQL Server to a subsidiary unit (Server B) SQL Server. The Linked Server connection tests successfully, and I can log into server A from server B and can using query. However, when I try to execute a query using a 4-part name through Linked server, I encounter the following error: Msg 8522, Level 16, State 3, Line 1 Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction. Environment Details: Head Office (server A) SQL Server: SQL Server 2008 R2 (already upgraded pack SP3) Windows Server 2012 TLS 1.2 enabled MS DTC service is turned on Subsidiary (server B) SQL Server: SQL Server 2016 Windows Server 2016 Standard (64-bit) TLS 1.2 enabled MS DTC service is turned on Networking: The B server connects via VPN to be on the same network as server A Ping and Telnet tests (IP and port) from both sides work fine SQL login from server A to server B(via IP and port) is successful USING PROVIDER: i have try to using SQLNCLI11 and MSOLEDBSQL, but both till error DTC. Linked Server test connection: Success Update, insert query: it's ok, but when i try selection query, i got error DTC has stop this transaction. example: SELECT * FROM [LinkedServerName].[DatabaseName].[dbo].table Has anyone faced a similar issue? Could this be a MS DTC configuration mismatch or network security/firewall/DTC port range issue? Any guidance on how to properly configure MS DTC across different servers/domains/VPNs would be highly appreciated. Thanks in advance!Permissions needed to set up linked server with out-of-process provider
First published on MSDN on Aug 19, 2010 When setting up linked server to third-party Databases, it is recommended to run the third-party provider in out-of-process mode, because when the provider is run in-process (within the same process as SQL Server), then any issues with the provider can affect SQL Server process which could also result in crashing SQL server.
Creating LinkedServer between On-Prem SQL to Azure SQL MI
Hi, is it possible to use security option (Be made using the login's current security context )with lnikedserver from on-prem SQL to MI ? Our MI is enabled for windows auth and we are successfully connect MI in SSMS using windows auth from on-prem client. when I use this option in linkedserver and try to access linked server with windows auth I get following error. Login failed for user '<token-identified principal>'. Reason: Could not find a user matching the name provided
Lesson Learned #473:Harnessing the Synergy of Linked Server, Python, and sp_execute_external_script
In an era where data management transcends individual database systems, SQL Server offers a sophisticated feature set that includes Linked Server integration, Python scripting, and the powerful sp_execute_external_script function. The main objective of this approach is to leverage a Python script within SQL Server using sp_execute_external_script connecting to other database outside of SQL Server On-premise, for example, Azure SQL Database or Azure SQL Managed Instance as an alternative to employing the pyodbc library. This method not only streamlines processes but also addresses key concerns in security and network configuration, such as opening ports, which are prevalent when using external libraries for database connections. By focusing on querying a Linked Server, we can achieve seamless data integration and manipulation while maintaining a secure and efficient environment.Lesson Learned #469:Implementing a Linked Server Alternative with Azure SQL Database and C#
In scenarios where direct Linked Server connections are not feasible, such as between Azure SQL Database and an on-premise SQL Server, developers often seek alternative solutions. This blog post introduces a C# implementation that simulates the functionality of a Linked Server for data transfer between Azure SQL Database and SQL Server, providing a flexible and efficient way to exchange data.Troubleshooting “Cannot create an instance of OLE DB provider”
First published on MSDN on Sep 28, 2011 Today I am going to blog about a frequent issue that we come across while creating linked server-:Cannot create an instance of OLE DB provider "MSDADASQL" for linked server "MyDB2"In this scenario I was creating a linked server to a DB2 server, but everything explained in this blog holds good for any linked server.Lesson Learned #63: It is possible to create Linked Server in Azure SQL Managed Instance?
First published on MSDN on Jan 19, 2019 The answer is Yes!!! and to create it you only need to follow up the instructions provided in this URL Unfortunately, nowadays, you only are able to use the SQL provider to connect to Azure SQL Database, SQL Server or Azure SQL Managed Instance.Intermittent ANONYMOUS LOGON of SQL Server linked server double hop
If you already know how to configure the double-hop for SQL Server linked server, you may go "RARE SCENARIO" section directly. Kerberos Delegation (double-hop scenario ) Here we will use Linked server scenario as an example. The 3 servers involved is Client, SQL Server 1 and SQL server 2. The first hop is from Client to SQL server 1 and the second hop is from SQL server 1 to SQL server 2. One of the most common double hop failure scenarios we handle during connectivity troubleshooting goes like this. Scenario I defined a linked server in a SQL Server instance running in machine A (middle server SQLBI), configured to connect to SQL Server running on machine B (backend server – SQLBI2). I am using the Be made using the login’s current security context security option for the linked server. That means you are using Integrated Security. If you test linked server connectivity, opening SQL Server Management Studio in machine A, you are able to connect just fine. Error Condition: If you open SQL Server Management Studio, in another machine C (SQLDW), connect to SQL Server in machine A and try to expand the linked server or run linked server query, you get the following error messages: SQL Server 2005 and later: “Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'”. Troubleshooting =================================== Checklist: - Verify SQL server(s) SPN exists (for both SQL server 1 and SQL server 2) - Verify the SQL SPN has the correct format under the correct service account - Verify no duplicated SQL SPN Make sure Service Principal Name (SPN) is registered for the SQL Server instance running on： Service Account Check (using SQL Server Configuration Manager). SPN =========================== List existing SPN for SQL service account: setspn -l SQLStartupAccountName Delete SPN: setspn -d MSSQLSvc/FQDN SQLStartupAccountName setspn -d MSSQLSvc/<FQDN>:<Port> < SQLStartupAccountName > setspn -d MSSQLSvc/SQLMachineName SQLStartupAccountName setspn -d MSSQLSvc/ SQLMachineName:<Port> < SQLStartupAccountName > Add SPN: setspn -A MSSQLSvc/<FQDN> < SQLStartupAccountName > setspn -A MSSQLSvc/<FQDN>:<Port> < SQLStartupAccountName > setspn -A MSSQLSvc/SQLMachineName < SQLStartupAccountName > setspn -A MSSQLSvc/SQLMachineName:<Port> < SQLStartupAccountName > If the client uses the server name to connect SQL Server, we should have the SPN for the server name. If the client uses the FQDN name to connect SQL Server, we should have the SPN for the FQDN name. If the client uses the DNS name to connect SQL Server, we should have the SPN for the DNS name. Search all duplicated SPN – For Windows 2008 and above only Setspn -X Search SPN example: setspn -F -Q mssqlsvr/test-sql2-2* Export all the SPN in order to check for duplicates ldifde -r (serviceprincipalname=*) -f allSPN.txt List all the SPN under an account ldifde -f spn.txt -d "dc=oceania,dc=corp,dc=anz,dc=com" -r "(samaccountname=aucemawdssqlsvc)" -l serviceprincipalname Delegation ===================== - Verify below option in AD for the SQL server 1 (i.e. the server in the middle) service account: Note: If SQL Server is running under a local system account, then below step should be performed on the SQL server 1 computer object instead.: Note: The Delegation tab appears only for accounts that have an assigned SPN. Constrained Delegation (recommended, more secure) - specify the SQL server service as SQL server 2 i.e. the last server in the hop as below: Or Un-constrained Delegation (also works, but less secure) - as below: - Verify the SQL server service account is in the local policy > user rights assignment > Impersonate a client after authentication - Verify the server name in the client connection string. The server name normally is FQDN or server short name. It can NOT be IP address as IP address cannot be used for Kerberos authentication. If the server short name is used, make sure that SPN is created for server short name. Note: FQDN is recommended. - When all the items in the checklist above are passed, open a problem to involve AD team to investigate further. RARE SCENARIO: ============================ The above information could help you to resolve all persist issues if that is caused by the wrong configuration. However, if you still face some intermittent “Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'”, please follow this section. When delegation is set to “Trust this computer for delegation to specified services only – Use Kerberos Only” (S4U2Proxy), double hop delegation may fail intermittently. This causes middle tier to access backend anonymously as there won’t be available ticket to access, and this could be rejected by backend server. This happened because S4U2Proxy relies on the service ticket sent from client to middle tier, in order to obtain a service ticket to backend. So, after the service ticket from client expires, they will be no evidence ticket available to serve the S4U2Proxy request. As a result, middle tier will need to start a new S4U2Self to first obtain the client user’s ticket to itself, but this would be rejected by DC with the error KRB_ERR_BADOPTION. Anonymous logon would be attempted from SQL1 to SQL2 and fail. Restarting the client application may temporarily fix the issue because the ticket will be purged if the application runs in its own session, so client will send a new service ticket to middle. This is expected behavior with linked Server scenario or any other application holding a logon session alive for more than 10 hours, where our evidence ticket from user gets expired Resolution: Adjust the option from “Trust this computer for delegation to specified services only – Use Kerberos Only” to “Use any protocol”. It will allow the S4U extension in Kerberos to obtain a service ticket when the existing one expires. This procedure is known as Protocol Transition, which allows the middle tier server to obtain a service ticket to itself and accomplish the Kerberos delegation. We do recommend administrators to go with this configuration in linked Server scenario and actually middle tier will still go through Kerberos authentication protocol when attempting to obtain the ticket to itself. More Information: When an application receives Kerberos ticket buffer from the client side, it passes the buffer to LSA using AcceptSecurityContext. There is an optional parameter to receive the expiry time for application when calling this function. If the application asks for this time, the return value will be set to (or before if we have other limitations there) the service ticket’s expiration time, and application can technically associate this expiry information with the client connection. This offers the options to application to check if we need to re-authenticate the user before processing any context in that session. Refer: AcceptSecurityContext function (sspi.h) - Win32 apps | Microsoft Learn However, not all applications are going to check this expiry time. Also, in certain scenarios, the expiry may not help much: Client is trying to connect to the server multiple times, so we have the chance to send a previous request service ticket (near to expiry). Or, long running connection in pool which doesn’t reauthenticate each time. This causes possibilities that the service ticket is cached for long on middle tier LSA. Middle tier may not connect to backend immediate after it authenticate the client. This is now observed on SQL Linked Database and Web Application deployment, as those configuration may have long running tasks or connection pools that cause LSA caches the expired ticket. Internal: To verify this, get network trace and Kerberos ETL trace on the middle tier (SQL1). Netmon: We should be able to identify that server is trying to perform S4U2Self Logon and get BAD Options error from KDC, which is supposed to not happen if we configured as “Use Kerberos Only”. Kerberos ETL: this should tell that we have ticket expired therefore in the session. In S4U2Proxy delegation situation, the first expired ticket should always be the evidence ticket - the one sent from client, because other tickets are requested using it and KDC use the nearest expiry among all limitations. Additional Reference: The UI tool makes things more easy =========================== You may try the UI tool “Kerberos Configuration Manager for SQL Server” to make checking easy. https://learn.microsoft.com/en-us/troubleshoot/sql/connect/using-kerberosmngr-sqlserver https://www.microsoft.com/en-us/download/details.aspx?id=39046 this tool has the “SPN” tag and “delegation” tag to cover both. This tool could connect to local server or remote server: SPN Check, using Kerberos Configuration Manager. System Tab. Do “refresh” before all steps： SPN Tab You can simple click on “Fix All” from the KCM or If you do not run the KCM without domain admin privilege, you may get the following error: Generate the SPNs from KCM and run the script on CMD window. Verify the delegation configuration for current SQL Server starting up account. à this tool can only check the delegation configuration but could not use the configure that. Please go above section to setup the delegation.
