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!
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
SQL Server Linked Server on local PC
Hello folks, Before the version below, which I have now Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Express Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 19044: ) I used this command/script to create a linked server for .csv file and everything worked fine, but today it creates a linked server without a table. EXEC sp_addlinkedserver @server ='test_server', @srvproduct='', @provider ='Microsoft.ACE.OLEDB.12.0', @datasrc='C:\Users\xxx123123\OneDrive\Desktop\Folder1\file1', @provstr='Text' I tried both OLEDB versions but still nothing. Microsoft.ACE.OLEDB.12.0 Microsoft.ACE.OLEDB.16.0 The final output looks like the picture below Also, I tried a command, but without success. EXEC sp_addlinkedserver test_server, N'Jet 4.0', N'Microsoft.Jet.OLEDB.4.0', N'C:\Users\xx123123\OneDrive\Desktop\Folder1\file1', NULL, N'Text'; Any idea how to create a linked server in the latest version of SQL Server? Thanks.
Adding AD Security group to a SQL linked server
Hello, I have a question about adding permissions for a user group on a linked server in SQL Server. I am not a security specialist, so I appeal to everyone. Here is the topo: I have private software that I'll call X installed on a Hyper-V virtual server. Users of this software connect through virtual machines using the Remote desktop. The application has its own SQL Server database instance. The security for this database is Windows Authenticated. Application X users all belong to a user group in the Windows Server 2019 Active Directory (AD). On the other hand, I have a Y application (Microsoft Dynamic 365) that is installed locally on each workstation and which has its own SQL instance. The application uses an SQL database which I would also call Y. As I would like to share information between the 2 SQL instances, I created a linked server on the X instance to the Y instance. I succeeded with some SQL query to insert, update, and delete some information in a database of instance Y from my instance X. To do this, I granted myself DB_owner rights on the database of X. I have reached the stage of allowing user group X to be able to run from application X using the same queries that interact on SQL server Y. However, I realize that I need to create each user of X in the SQL instance of application Y from the Security, Connection menu. My requests work when I grant DB_Owner roles. I would have thought that I could have used the same group used in AD for my X application. I tried well but it doesn't work. I don't know if this is due to the configuration of my linked server. Here is the configuration I am using: Could someone point me to a solution to avoid having to recreate every user of my AD from X in my instance Y and just link my group X.
