Ms sql linked server ошибки

I’m trying to pull in data from a remote SQL Server. I can access the remote server using SQL authentication; I haven’t had any luck using the same credentials with sp_addlinkedserver.

I’m trying something like this:

Exec sp_dropserver 'Remote', 'droplogins'
go

EXEC sp_addlinkedserver   
   @server='Remote', 
   @srvproduct='',
   @provider='SQLNCLI', 
   @datasrc='0.0.0.0'

EXEC sp_addlinkedsrvlogin
    @useself='FALSE',
    @rmtsrvname='Remote',
    @rmtuser='User',
    @rmtpassword='Secret'

Select Top 10 * from Remote.DatabaseName.dbo.TableName

Here’s what I get:

OLE DB provider "SQLNCLI" for linked server "Remote" returned message 
"Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "Remote" returned message 
"An error has occurred while establishing a connection to the server. 
When connecting to SQL Server 2005, this failure may be caused by the 
fact that under the default settings SQL Server does not allow remote 
connections.".

Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53]. 

Again, I can access the server directly (in SQL Management Studio) using these exact credentials, so it’s not a problem with my network or credentials.

Most of the examples I’ve seen online seem to involve Windows domain accounts, as opposed to SQL security logins. Does this not work with SQL authentication?

Abstract

This article details the use of «Execute AT LinkedServer» clause. It’s best in some ways when you are trying to run dynamic complex queries across heterogeneous data source. There are many instances that OpenQuery/OpenRowSet and four-part qualifier calling
might not work in the complex SQL design.  The limitation of a linked server will explode when are trying to manipulate data and write complex queries with heterogeneous data sources.  

↑ Return to Top

↑ Return to Top

Introduction

We recently ended up in calling a remote stored procedure(“X”) from a remote machine (“Y”) and the script needed to pull data and store into a remote server(“Z”).  We set up the linked server and everything was working great. Executing queries to pull the
data to my machine was fine, but a problem arose when we needed to execute a stored procedure which houses complex SQL queries from the other remote server.

The three methods we have tried to execute the SP on remote server are:

• Calling with four-part naming convention
• Using OpenQuery and OpenRowSet
• Execute At LinkedServer

The first method was not successful since there is a dependency on inbound and outbound transactions. It was successful in the execution of the SP using Execute(‘SQL’) AT LinkedServer. The details are illustrated in this article.

↑ Return to Top

Pre-requisites

• Make sure RPC and RPC Out parameters are set to TRUE
• MSDTC is enabled to run distributed queries

Syntax

E xample, 

SQL could be a single query or a sequence of statements, dynamic SQL, or be entirely static. The linked server could be an instance of SQL, Oracle, DB2, etc. The use of Openquery, Openrowset and four-part calling stored procedure might work in simple
cases. When you are working with distributed queries with heterogeneous data source the use of EXECUTE … AT LinkedServer works best. The SQL Server extends the EXECUTE statement so that it can be used to send pass-through commands to linked servers. Additionally,
the context in which a string or command is executed can be explicitly set.

↑ Return to Top

Known Errors

Error 1: The object has no columns or the current user does not have permissions on that object

Error 2: Unable to begin Distributed transaction

«The operation could not be performed because OLE DB provider «SQLNCLI» for linked server «X» was unable to begin a distributed transaction»

To re-enable the RCP commands for the linked server:

exec sp_serveroption @server='SERVERNAME1', @optname='rpc', @optvalue='true'
exec sp_serveroption @server='SERVERNAME1', @optname='rpc out', @optvalue='true'

Calling remote SP with four-part qualifier name.

EXEC ADBSP18.DW_PROD.dbo. [trans_Tracs_Pull] '7/1/2016','7/31/2016'

Error 3: The Transaction Manager has disabled its support for remote/network transactions

In this case, the remote server was configured with IBM I-Series driver.

•  Open “Component Services” Control Panel > Administrative Tools > Component Services

•  In Component Services, right click “My Computer” and select “Properties” Console Root > Component Services > Computers > My Computer

•  Select the “MSDTC” tab (Select appropriate MSDTC if it’s clustered) and click “Security Configuration” in the “Transaction Configuration”

Enable Allow inbound and Allow outbound.

•  Restart the DTC service (should do so automatically).

The below example shows the execution of distributed query executed on the remote server which fetches the data and writes it an another remote SQL instance.

↑ Return to Top

Conclusion

You can safely use Linked Servers in a production setting but do your research and test before settling on a solution. In many cases, the simple approach may ease out the problem. 

The OPENQUERY/OPENROWSET guarantees the SQL will execute on the remote server and only bring back the results from that query to the local server.In many cases, this can be used for simple SQL statements and it row set doesn’t
hold in any temporary tables. When the SQL is getting complex and needs to query heterogeneous data source, It may get little cumbersome. EXEC() AT is similar to OPENQUERY/OPENQUERY in that the static SQL/dynamic SQL will always execute on the remote server
except that you cannot use the results locally within a JOIN but you can use them, however, to INSERT them into a local table. Also, EXEC() AT allows you to provide SQL in a variable whereas OPENQUERY will not accept a variable which many times forces the
use of dynamic SQL.

References

Technet 

EXECUTE (Transact-SQL)

• https://technet.microsoft.com/en-us/library/ms188332(v=sql.100).aspx

Further Reading

• http://dba.stackexchange.com/questions/46289/which-one-is-more-efficient-select-from-linked-server-or-insert-into-linked-ser/46302#46302

↑ Return to Top

Problem:

You created a linked server on SQL Server to a PostgreSQL database. You can open and navigate the linked server on SSMS up to listing the tables, but can not perform select to certain tables, returning the below errors:

The data type throwing the error can either be a varchar, text, or Boolean. It also happens whether the specific column is included in the select statement or not.

Cause:

This happens because of a mismatch with the length of the data type or an incorrect conversion from the source data type in PostgreSQL and SQL Server.

Workaround:

You can work around this issue by force casting the selected column to a specific data type. This solution will work for data types that have different lengths on Postgresql and SQL Server. It will not work for incorrect conversions.  eg. the column in PostgreSQL is a Boolean and the linked server want to receive it as a char value.  For the proper solution, refer to to the section below.

Solution:

To address the issue properly, it all comes down to creating your Linked server correctly. Here are the proper steps for creating a SQL Server linked server to a PostgreSQL database:

1. Download and install the latest ODBC driver for the PostgreSQL version that you are using. There used to be a lot of third party ODBC for PostgreSQL since they didn’t come out of one for 64 bit – and most SQL Server deployments out there are 64 bit. But now PostgreSQL has released their own. You can download the drivers here.

2. After installing the driver, create a 64 bit ODBC System DSN pointing to your PostgreSQL. Launch the ODBC Data Source Administrator on the server where the SQL Server is. If you want the DSN to be available for everyone which you should if you are going to use it for the linked server, go to the System DSN tab. Click Add.

3. Now there are two drivers that the Postgres has added. One for Unicode and a default one. The Unicode driver is for modern applications that have wider character sets and for Postgresql databases that are encoded with UTF-8/Unicode. You should likely be using this. The other details are self-explanatory. Those are the details for your PostgreSQL, database, server, user name, SSL Mode, port, password, description for the DSN, and the data source which will be the name of your DSN. You can click test after filling out the details to verify the connectivity.

4. Now this is the important part if you are receiving the conversion errors mentioned in this post. Under Options, click on Datasource. This will launch the Advanced Options window for the Datasource. From here you can set how the DSN will treat the data being retrieved from the source.

5. You can set how it will treat certain data types. For this scenario/issue, you’ll need to uncheck the “Bools as Char” under data type options and treat Text as LongVarChar. You can also set Unknown Sizes to Maximum. The Max Varchar and MaxLongVarChar values can be left as default. But if you are hitting issues with length for varchar data types, you may adjust them accordingly.

6. Now that you have your ODBC DSN set correctly, you can recreate/create your Linked Server.  You can use SSMS GUI. Log in to your SQL Server and navigate to Server Objects -> Right click Linked Servers and choose New Linked Servers.

7.  Key in the name you want for your linked server. Choose Other Data source on server type and choose Microsoft OLE DB Provider for ODBC Drivers. You can put PostgreSQL on Product Name for clarity, then on Data Source input the DSN Name you created earlier.

8. Choose Security on the Left tab to configure the security for the linked server. Choose “Be made using this security context” and key in your username and password.

9. Choose Server Options on the left tab to change some settings as shown below. Note the changes on RPC settings.

10. Click OK. Now it will attempt to connect first. If all is well, it will be able to save and appear on your Linked Server list. If it can’t connect currently it will tell you.

11. Now try selecting again. You should be able to select properly without encountering the errors.

12. You can also create the SQL Server Linked Server using the TSQL script below:

USE [master]

go

EXEC master.dbo.Sp_addlinkedserver   @server = N’LinkedServerName’,   @srvproduct=N’Postgresql’,   @provider=N’MSDASQL’,
@datasrc=N’DSN.Name’
go

EXEC master.dbo.Sp_serveroption   @server=N’LinkedServerName’,   @optname=N’collation compatible’,   @optvalue=N’false’
go

EXEC master.dbo.Sp_serveroption   @server=N’LinkedServerName’,   @optname=N’data access’,   @optvalue=N’true’
go

EXEC master.dbo.Sp_serveroption   @server=N’LinkedServerName’,   @optname=N’dist’,   @optvalue=N’false’
go

EXEC master.dbo.Sp_serveroption   @server=N’LinkedServerName’,   @optname=N’pub’,   @optvalue=N’false’
go

EXEC master.dbo.Sp_serveroption   @server=N’LinkedServerName’,   @optname=N’rpc’,   @optvalue=N’true’
go

EXEC master.dbo.Sp_serveroption   @server=N’LinkedServerName’,   @optname=N’rpc out’,   @optvalue=N’false’
go

EXEC master.dbo.Sp_serveroption   @server=N’LinkedServerName’,   @optname=N’sub’,   @optvalue=N’false’
go

EXEC master.dbo.Sp_serveroption   @server=N’LinkedServerName’,   @optname=N’connect timeout’,   @optvalue=N’0′
go

EXEC master.dbo.Sp_serveroption   @server=N’LinkedServerName’,   @optname=N’collation name’,   @optvalue=NULL
go

EXEC master.dbo.Sp_serveroption   @server=N’LinkedServerName’,   @optname=N’lazy schema validation’,   @optvalue=N’false’
go

EXEC master.dbo.Sp_serveroption   @server=N’LinkedServerName’,   @optname=N’query timeout’,   @optvalue=N’0′
go

EXEC master.dbo.Sp_serveroption   @server=N’LinkedServerName’,   @optname=N’use remote collation’,   @optvalue=N’true’
go

EXEC master.dbo.Sp_serveroption   @server=N’LinkedServerName’,   @optname=N’remote proc transaction promotion’,   @optvalue=N’true’
go

USE [master]
go

EXEC master.dbo.Sp_addlinkedsrvlogin   @rmtsrvname = N’LinkedServerName’,   @locallogin = NULL,   @useself = N’False’,   @rmtuser = N’pgsqlUserName’,   @rmtpassword = N’pgsqlUserNamePasswordHere’
go

This should address the conversion issues and you’ll be able to select without any issue.

Want to talk with an expert? Schedule a call with our team to get the conversation started.

Your error here is

server not found

This is because you used @srvproduct=N'SQL Server', in this case you should use SQL Server’s pc name in case of default instance, i.e. this connection string would be correct in case of default instance:

EXEC master.dbo.sp_addlinkedserver @server = N'my_pc_name', @srvproduct=N'SQL Server'

or use this one in case of named instance:

EXEC master.dbo.sp_addlinkedserver @server = N'my_pc_nameinst_name', @srvproduct=N'SQL Server'

If you want to use any other name, even just IP (or IP,port in case of named instance, use @provider=N'SQLNCLI':

for default instance

EXEC master.dbo.sp_addlinkedserver @server = N'my_linked_server_name_as_I_want_it', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'10.237.69.11'

for named instance:

EXEC master.dbo.sp_addlinkedserver @server = N'my_linked_server_name_as_I_want_it', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'10.237.69.11,your_port'

If you want to use GUI (SSMS), or change server name to your pc name, or use another radio button position, for ex. SQL Native Client (other than SQL Server)

Creating a new Linked Server in MSSQL seems to be one of the easiest DBA’s tasks. It might however happen that some problems occur. How to deal with a failed connection test? Let me describe the issue I faced and how I solved it. 

What is a Linked Server? 

Linked Servers are used to enable one SQL Server Database Engine to read data from another remote datasource and execute T-SQL statements on remote database objects. The remote data source does not have to necessarily be only another SQL Server instance, the provider might be i.a. Oracle database or Azure CosmosDB. The purpose of creating a Linked Server is to enable quick and seamless data integration between various data sources

Required Permissions 

Let’s imagine that our internal client asks us to join a short 15-minute meeting and expects us to create a Linked Server during that call. We are informed that it’s a very typical implementation, which has already been introduced on another internal environment and we will be provided will all necessary information. The only reason we are needed on that call is that because we obey the security rules and do not grant sysadmin rights to anyone outside our DBA team, which helps us keep an eye on all significant events on our DB environment. 

Linked Server successfully created but connection test failed 

So coming back to our case study, the stakeholder provided us with the query which was used on another instance and they claim that it should work with no issues. Our job was just to click “Execute” on the supposedly-perfect query from our admin account and to report success. Well, the Linked Server has beed created, but the simple test connection failed. Why? Let’s discover. 

Please note that for this article’s purpose I’ll show the GUI troubleshooting path, not the T-SQL query. 

So we’ve been provided with the Linked Server name, data source Provider, source server address (Data source) and the database name (Catalog). 

Want to talk with an expert? Schedule a call with our team to get the conversation started.

Your error here is

server not found

This is because you used @srvproduct=N'SQL Server', in this case you should use SQL Server’s pc name in case of default instance, i.e. this connection string would be correct in case of default instance:

EXEC master.dbo.sp_addlinkedserver @server = N'my_pc_name', @srvproduct=N'SQL Server'

or use this one in case of named instance:

EXEC master.dbo.sp_addlinkedserver @server = N'my_pc_nameinst_name', @srvproduct=N'SQL Server'

If you want to use any other name, even just IP (or IP,port in case of named instance, use @provider=N'SQLNCLI':

for default instance

EXEC master.dbo.sp_addlinkedserver @server = N'my_linked_server_name_as_I_want_it', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'10.237.69.11'

for named instance:

EXEC master.dbo.sp_addlinkedserver @server = N'my_linked_server_name_as_I_want_it', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'10.237.69.11,your_port'

If you want to use GUI (SSMS), or change server name to your pc name, or use another radio button position, for ex. SQL Native Client (other than SQL Server)

Creating a new Linked Server in MSSQL seems to be one of the easiest DBA’s tasks. It might however happen that some problems occur. How to deal with a failed connection test? Let me describe the issue I faced and how I solved it. 

What is a Linked Server? 

Linked Servers are used to enable one SQL Server Database Engine to read data from another remote datasource and execute T-SQL statements on remote database objects. The remote data source does not have to necessarily be only another SQL Server instance, the provider might be i.a. Oracle database or Azure CosmosDB. The purpose of creating a Linked Server is to enable quick and seamless data integration between various data sources

Required Permissions 

Let’s imagine that our internal client asks us to join a short 15-minute meeting and expects us to create a Linked Server during that call. We are informed that it’s a very typical implementation, which has already been introduced on another internal environment and we will be provided will all necessary information. The only reason we are needed on that call is that because we obey the security rules and do not grant sysadmin rights to anyone outside our DBA team, which helps us keep an eye on all significant events on our DB environment. 

Linked Server successfully created but connection test failed 

So coming back to our case study, the stakeholder provided us with the query which was used on another instance and they claim that it should work with no issues. Our job was just to click “Execute” on the supposedly-perfect query from our admin account and to report success. Well, the Linked Server has beed created, but the simple test connection failed. Why? Let’s discover. 

Please note that for this article’s purpose I’ll show the GUI troubleshooting path, not the T-SQL query. 

So we’ve been provided with the Linked Server name, data source Provider, source server address (Data source) and the database name (Catalog). 

In the Security tab we’re to choose the fourth option – the connection will be made using this security option – all we have to do is just to enter the username and password we’ve received from the client. 

And that’s it. We know the source server address, the source database name and we have a user’s credentials which has proper access scope. We’re clicking OK. What could go wrong? 

Yikes! The error dialog appears: 

Unfortunately, something went wrong. Let’s cautiously read what the error message says, it might be help us chose what to do next: 

• Could not open a connection to SQL Server, 
• Login timeout expired, 
• A network-related or instance-specific error has occured while establishing a connection to SQL Server. Serves is not found or not accessible  – It’s a very common Error 53, suggesting that the server address is either incorrect or it does not allow remote connections. 

Solve your problem with these steps

I’ll provide the troubleshooting path which I followed and finally found out how to successfully establish connection between my server and the one we’re trying to link to. If my solution does not fit for you, I’d suggest firstly contacting the source server’s DBA and asking him to confirm if they do allow remote connections. This can be verified by opening SQL Server’s instance properties > Connections tab. 

Before contacting another DBA, the steps I’ve taken were:

1. Check for typos and pinged the source server

Obvious – but worth checking. Double-check if you’ve properly entered/pasted the server address, database name, user’s credentials. Try pinging the server you’re supposed to connect to. Ping request could not find host <hostname> might be alarming 

2. Check the user’s permissions

A simple verification we may do is to try to log in with the provided credentials to the source server. 

The error I got is clear: my machine is not able to recognize the host we’re trying to connect to. 

Perfect, then we are almost there. No woder why ping request could not find the host if we’re trying to connect to an external domain (we should’ve asked the stakeholder to describe the source server’s parameters more precisely). Anyway, the solution is very simple – we have to edit the hosts file and help our local machine connect to an external source. 

3. Add the server IP and DNS in the hosts file

On the server where your SQL Server engine is installed go to the following file: C:Windowssystem32driversetchosts. 

Before changing any system file – don’t forget to save its copy somewhere else – just in case. Let’s open the hosts file in the Notepad and add in the last row the IP address and DNS name of the desired server. 

Just to remind – the “#” symbol serves for commenting out the rest of the row. In our case we do not want the hash at the beginning of our entry. After entering the correct value, save the file and go back to SQL Server Management Studio. 

4. Repeat step 2 – try to connect with the user’s credentials

Let’s try connecting again to check if the error message has changed. 

The message “no such host known” does not appear anymore! In my case the error says that the user does not have access to the master database which is its default database. In our case it’s not a problem, since the Linked Server is configured to connect to a particular database. Step 5 is optional – if you don’t have time to satisfy your curiosity, go directly to step 6. 

5. Connect to a non-default database 

Choose the SQL Server Authentication option, enter the server name and user credentials and do not click automatically Connect but click Options >> in the right bottom corner. 

Next change the database name from <default> to the one you want to connect to. 

After clicking Connect we’ve been successfully logged on the source server: 

Then we can get back to the Linked Server creation. 

6. Create the Linked Server

Finally, let’s re-enter the parameters the customer provided us with and try to create the Linked Server again. In my case no error message appeared and the Linked Server object has been successfully created. 

Right-click on the newly created Linked Server and choose Test Connection. 

The reply we get is that the test connection was successful. The job is done! 

The reason we failed to create the Linked Server immediately with the information provided by the requestor was not due to insufficient data from the client nor incorrect credentials. What we missed was asking ourselves (or the client) – where is the source server located. As it was not in our corporate domain, it was our duty to help our local machine connect to an external source by adding a row in the hosts file. 

Any questions on how to configure a Linked Server? Are you wondering how such solution may boost your business? 

Schedule a free consultation. Consult your company needs with our experts. Learn about solutions that will help your company improve business processes and ensure data security.