Questions tagged [linked-server]
Linked server allows operation to multiple server as one query.
623
questions
3
votes
0
answers
1k
views
Lined Server - Cannot create an instance of OLE DB provider "IBMDADB2.DB2COPY2" for linked server "MYDB2"
I would like to create Linked Server from SQL Server 2016 on Windows to Db2 v11.5.7 on Linux/x86.
I created linked Server using Microsoft OLE DB Provider for DB2 and it works fine.
Now I would like to ...
- 339
0
votes
0
answers
1k
views
How to use "with execute as" while querying a linked server from SQL Server
I have a strange permission issue with SQL Server 2019 (same thing happened also with 2016 version).
I have a database ReportDB, where reportuser (login: reportlogin) can run several reports.
I also ...
- 11
0
votes
1
answer
68
views
How to test whether current sql engine service account can decrypt the SMK?
Is it true that when the sql service account is changed via the config manager then the new account can decrypt the SMK.
However if the change was made via the services.msc, then the new account doesn'...
- 3,593
0
votes
1
answer
509
views
Do linked servers stop working after migrating the system dbs to a new sever?
Link: https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-service-master-key-transact-sql?view=sql-server-ver15
The service master key is automatically generated the first time it is
needed ...
- 3,593
0
votes
1
answer
1k
views
Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for all but Windows Administrator login
I have a few dozen linked servers, MSSQL, Teradata and Oracle. These are on SQL Server 2019. Oracle servers are using OraOLEDB.Oracle 19c client. They are all created with the same parametrized script....
- 896
1
vote
2
answers
2k
views
How to save result from a Stored Procedure in a Linked Server to a variable or table?
I need to read the results of a stored procedure in a linked server, and store them in a variable, or a temporary table if not possible.
I can do this, and I get the correct result:
EXEC ('EXEC DBName....
0
votes
1
answer
357
views
Openquery through Oledb driver for DB2 slows down after a while - reboot only solution?
We have several sql servers that read from a db2 database on an as400 via Openquery to a linked server using the Microsoft oledb driver for db2 v5. One sql instance had a problem where its selects ...
- 83
0
votes
0
answers
1k
views
Linked Server Error Msg 7321, Level 16, State 2, Line 1
I am getting the following error when I execute a simple linked server query.
OLE DB provider "MSDASQL" for linked server "name" returned message "HTTP request returned status ...
- 83
1
vote
1
answer
7k
views
Linked server error- The table either does not exist or the current user does not have permissions on that table
There is a view that is being called from Server A (SQL 2016) and this view needs to read data from Server B (SQL 2014). There is a linked server set up between these two servers and the connection ...
- 83
5
votes
1
answer
227
views
Door Number 1, 2, or 3?...Security change, bug, or failed upgrade with an in-place upgrade from SQL Server 2016 to SQL Server 2019?
What I Did:
Recently I tested doing an in-place upgrade on ServerA, a development server that was running SQL Server 2016 (SP 2 CU17) Standard Edition to SQL Server 2019 Standard Edition. (I know this ...
- 37.5k
1
vote
1
answer
535
views
How to create a SQL Server linked Server with Local SQL Server Authentication User to a remote Windows Authentication
I need to create a Linked Server from Server A that uses a SQL Server Authentication to a Server B that uses a Windows Authentication.
Is the "impersonation" possible?
If "YES" ...
- 13
0
votes
0
answers
496
views
Delay in prelogin response when connecting to Azure SQL
I have set up a linked server to an Azure SQL 2014 managed instance. The vendor gave me a SQL login and password to use. I can open a direct connection in SSMS and run queries. But when I try to run ...
- 129
-2
votes
1
answer
864
views
Need to create a SQL Server Linked Server to an Oracle data source [closed]
Can someone kindly point me to a good documentation where I can get all the information I need to be able to pull data out of an oracle database from a SQL Server database please? For example, what ...
- 327
0
votes
0
answers
245
views
MS SQL Linked Server Insert Error
As part of data migration, I have created linked server for Greenplum database in MS SQL 2019. I am able to query using linked server. Select & insert commands are working fine for most of the ...
- 1
0
votes
0
answers
895
views
Linked Server Connection Issue to SQL Server AlwaysOn Listener
I have SQL Server 2008R2 Standard Edition. I am making a Linked Server connection to a Listener of an AlwaysOn Availability Group.
However, when I run a query from SSMS I intermittently get this error:...
- 1,394
4
votes
2
answers
1k
views
SQL Server - Is my database being queried over linked server?
Is there a way to know if a specific database on my SQL Server instance is being queried over linked server?
- 217
0
votes
1
answer
122
views
Merge 2 SQL Server Instances?
I have 2 SQL Server instances on a single server, inst1 and inst2. These are tightly coupled instance, with inst1 being linked to inst2 and vice-versa.
Because they are already running on the same ...
- 217
-1
votes
2
answers
982
views
Linked server - When is it recommended to use SQL Server or the 4 Other data sources for SQL Server?
To configure linked server pointing to a SQL server, there are 4 possible options.
Server Type: SQL Server
Server Type: Other data source; Microsoft OLE DB Provider for SQL Server
Server Type: ...
- 3,593
1
vote
1
answer
409
views
Should the remote user value be configured to map users defined in Local login or map users not defined in Login login?
Link: https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-ver15#to-create-a-linked-server-to-another-instance-of-...
- 3,593
-2
votes
1
answer
944
views
Does linked server local login value support a windows AD group based sql login?
Link: https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-ver15#to-create-a-linked-server-to-another-instance-of-...
- 3,593
1
vote
0
answers
987
views
Problem creating a link between SQL Server Standard edition and SQL Express edition
I am trying to establish a connection between two SQL severs located in different location and trying to read/fetch the tables. When I connect the remote Standard SQL Server from SQL Server Express ...
- 13
0
votes
0
answers
533
views
Sql Job Failing with Timeout Error
Recently I have noticed that a job is failing on our Production 2014 SQL Server with this error text:
Timeout error [258]. [SQLSTATE 42000] (Error 258) OLE DB provider "SQLNCLI11" for ...
- 83
2
votes
1
answer
243
views
Updating varchar column over linked server with parameterized query causes remote scan and cursorfetch [closed]
I'm issuing a fairly simple update of single varchar column against a remote linked server. E.g.:
UPDATE Hydrogen.CRM.dbo.Customers
SET EyeColor = 'Blue'
WHERE CustomerID = 619
And that works fine ...
- 976
0
votes
1
answer
2k
views
Kerberos - configure constrained delegation using SQL Server's virtual accounts
I'm trying to setup Kerberos constrained delegation to solve my double hop problem.
I'm using standard SQL Server configuration with Virtual Account (NT SERVICE\MSSQLSERVER) as a service account for ...
- 141
0
votes
1
answer
2k
views
SQL Server - managing Linked Servers with domain authentication
We are small IT department and we are changing how our infrastructure works. We are trying to make it more secure, manageable and transparent for auditing.
We are implementing a domain for our servers ...
- 141
-1
votes
2
answers
560
views
Accessing SQL server on-Prem database views from remote server through Azure Data Factory
We need to copy the data from client's remote SQL server (on-prem) database to our Azure SQL server through Azure data factory.
So we can automate the data pull on regular basis.
Client tried to offer ...
2
votes
0
answers
120
views
SQL Server - Holistic approach to managing permissions from a domain [closed]
We are small IT department and we are changing how our infrastructure works. We are trying to make it more secure, manageable and transparent for auditing.
We are implementing a domain for our servers ...
- 141
1
vote
1
answer
441
views
MS SQL server - linked server target configuration
I am setting up a linked server using kerberos authentication, but I am running into a problem that I have not seen before. I have set up linked servers using kerberos previously without problems.
On ...
1
vote
0
answers
116
views
SQL Server to PostgreSQL with unique Constraint
I am trying to push data from a MS SQL Server to a PostgreSQL (both on my local device) by using the pg as linked server and calling a stored procedure of the SQL Server.
A simple
INSERT INTO [...
- 111
0
votes
0
answers
1k
views
Linked Server using provider string gives error "Access to the remote server is denied because no login-mapping exists" when accessing via SQL login
I know there's another question on DBA.SE that is about this issue, but the solution there from sepupic & Paul White doesn't fix my problem -- hence, a new question.
LocalMSSQL is running SQL ...
- 454
0
votes
1
answer
169
views
How to modify my existing T-SQL query to work for a linked server?
I am using SQL Server 2014 and I need to modify my existing T-SQL query so that it works for a linked server.
My existing codes are as follows:
USE [MyDatabase]
SELECT name
FROM sysobjects
WHERE id ...
- 123
0
votes
1
answer
1k
views
Linked server only works if I am on the machine itself. I can't use it from my local computer. what is missing?
I have a few queries that I use to monitor log shipping. Sometimes I want to run those queries on a different server. I usually do that through a linked server.
I have a server called SQLDEV4-TS that ...
- 16.2k
0
votes
0
answers
146
views
Linked Server Issues SQL Server to Oracle
I am trying to set up a linked server from SQL Server Production to one of our Oracle instances but experiencing this error :
The test connection in ODBC succeeds, but it fails on SQL Server side. I ...
- 83
2
votes
1
answer
2k
views
Adding AD Security group to a SQL linked server
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'...
- 55
0
votes
1
answer
1k
views
Getting data from Postgres through Linked server of SQL
I am trying to get the data from PostgreSql db through linked server of the SQL Server. But getting the following error on running this query
select * from openquery(SALESFORCE,'select closeddate
...
- 99
1
vote
1
answer
564
views
How to get an actual execution plan from the linked server?
There is a "main" SQL Server to which I have full access and I can connect to it via SSMS. Its version is:
Microsoft SQL Server 2016 (SP1-CU15-GDR) (KB4505221) - 13.0.4604.0 (X64)
Jun ...
- 4,647
0
votes
1
answer
2k
views
Providers in SQL Server linked server configuration
How 'allow in process' works for providers in SQL server linked server configuration ? It looks like it's unchecked by default. What is the requirement for keeping this option checked and What are the ...
- 25
1
vote
1
answer
2k
views
SQL Server 2019 using Linked server to Oracle 19c (19.9) using Oracle 19c client on Windows - DBCOLUMNFLAGS_ISNULLABLE error
Having issues selected data for some Oracle tables over the linked server from SQL Server:
select type, count(*) from RMTDB..JOB1308.HOST_INTERFACE group by type order by 1 desc;
Msg 7356, Level 16, ...
- 101
1
vote
1
answer
3k
views
Some users fail to query linked server with "Login failed for user NT AUTHORITY\ANONYMOUS LOGON"
When I try to run a simple query involving a linked server it fails:
SELECT * FROM [server2].[DWH].[dbo].[SomeTable]
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS ...
- 4,647
9
votes
5
answers
5k
views
Linked Server connection started failing with "Cannot generate SSPI context ... SQL Server Network Interfaces: The logon attempt failed" error?
The Issue
I have two SQL Server 2016 Standard Edition servers on the same domain. ServerA has two linked server connections setup to ServerB.
The first linked server connection is using a dedicated ...
- 37.5k
0
votes
1
answer
2k
views
Linked Server to Oracle error ORA-01804
Migrating SQL Server to a new machine. Migration involves setting up a number of linked servers, some SQL Server, Oracle and Teradata. No issues with SQL Server or Teradata servers, but every Oracle ...
- 896
0
votes
0
answers
325
views
Sql Server ODBC Linked Server
I'm trying to create a linked server in SQL server 2017, linked system is odbc (criteria criOdbc)
Test 1 Form SQL Server SSMS
select * from OPENROWSET('MSDASQL','DSN=criteria64;uid=xxxx;pwd=yyyy;','...
- 143
1
vote
1
answer
514
views
Can SQL Server OpenQuery/OpenRowSet to an LDAP Linked Server be used for Authentication?
I can fetch data back from LDAPS (port 636/3269) by first creating a Linked Server using sp_addlinkedserver and sp_addlinkedsrvlogin.
However, what I really need to do is to authenticate a Username/...
- 173
1
vote
1
answer
8k
views
Cannot fetch a row from OLE DB provider "SQLNCLI11" for linked server
I have two SQL Servers on two different machines somewhere in the cloud (maybe Azure).
One is Microsoft SQL Server 2012 (SP3-CU10) (KB4025925) - 11.0.6607.3 (X64)
Jul 8 2017 16:43:40
Copyright (c) ...
- 4,647
0
votes
1
answer
2k
views
Can an AD service account host SPNs for multiple SQL Instances?
Trying to set up Kerberos Authentication between two linked server to get past the double-hop issue.
Server A is in a domain with several other SQL Servers. All SQL instances on all servers in this ...
- 365
0
votes
0
answers
484
views
Linked server anonymous login error
I am trying to access a database in my UAT server through a windows login credential. When I try to access the database, I get the error Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' This ...
- 83
0
votes
1
answer
916
views
Create LINKED SERVER to MYSQL using SSH
Is there any way to create a MS SQL Server Linked Server to connect to MySQL through SSH?
I managed to connect to MySQL through SSH using other database management tools like HeidiSQL or DBeaver, ...
- 41
0
votes
0
answers
144
views
Linked Server Using a DNS Alias Returning Bad Data
Somehow on the same DEV server we have two Linked Servers to the same PROD server. While that, in and of itself, is not an issue; we have noted something we can't explain and we wonder of anyone else ...
- 135
4
votes
2
answers
415
views
Remote Scan when updating using functions
Why does using ISNULL() when updating through a linked server use Remote Scan to retrieve all rows and filter locally instead of filtering through Remote Query?
UPDATE LINKEDSERVER1.database1.dbo....
- 167
0
votes
0
answers
423
views
SQL Server linked server losing Windows authentication credentials
We are seeing a sporadic authentication issue between two SQL Server instances with linked servers where the Windows authentication credentials are sometimes lost on the remote server. When this ...
- 287