On demand creation and destruction of a SQL Server Linked Server

By:   |   Updated: 2009-09-25   |   Comments (8)   |   Related: 1 | 2 | 3 | 4 | > Linked Servers


Problem

I am trying to create a stored procedure which creates a linked server connection, performs a query against the linked server, and then tears down the linked server connection. I don't want the connection to be up at all times, hence the reason I want to create the connection in the stored procedure. However, when I try to create the stored procedure, I get the following error:

Msg 7202, Level 11, State 2, Procedure usp_GetDatabasesFromRemoteServer, Line 8> Could not find server 'LinkingServer' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

How do I get around this and create the stored procedure?

Solution

The trick to solving this problem is to create the linked server before executing the CREATE PROCEDURE statement. What's happening here is SQL Server is trying to validate the syntax of the stored procedure and cannot find the reference to "LinkingServer".

For instance, if you have this query within the body of the stored procedure this is what SQL Server is failing on when trying to create the procedure.

SELECT name FROM [LinkingServer].master.sys.databases;

SQL Server has to have the reference to "LinkingServer" defined or it can't validate the query. As a result, the stored procedure creation will fail.

To get around this issue, you'll need to manually execute sp_addlinkedserver to create the connection as shown below and then create the stored procedure.

EXEC sys.sp_addlinkedserver @server = 'LinkingServer', @srvproduct = N'SQL Server';

Once the linked server has been created, you can create the stored procedure. And once the stored procedure has been created you can drop the linked server, because the SP should create it the next time it runs.

One other step I take within the stored procedure is to check for the existence of the linked server. The reason for this is if the linked server already exists because it didn't get cleaned up properly or because someone else created it, the stored procedure will throw an error when it runs. This is easy to do using a query against sys.servers for the name of the server.

The following is an example of a stored procedure where such a check is being made along with the creation and destruction of the linked server.

CREATE PROC dbo.usp_GetDatabasesFromRemoteServer 
AS 
BEGIN 

  IF NOT EXISTS(SELECT name FROM sys.servers WHERE name = 'LinkingServer') 
    EXEC sys.sp_addlinkedserver @server = 'LinkingServer', @srvproduct = N'SQL Server'; 
     
  SELECT name FROM [LinkingServer].master.sys.databases; 
   
  EXEC sys.sp_dropserver @server = 'LinkingServer', @droplogins = 'droplogins'; 
END; 
GO

If the query against sys.servers finds the "LinkingServer" linked server connection already exists, it will not attempt to create the linked server connection. By the way, this also ensures the stored procedure runs properly the first time you execute it, even if you forgot to use sp_dropserver to remove the linked server connection you built when trying to create the stored procedure. Without this sort of logic, you might see the following error any time the query runs and that linked server is already present:

Msg 15028, Level 16, State 1, Procedure sp_addlinkedserver, Line 82 The server 'LinkingServer' already exists.

While the query (the SELECT name ...) will still execute properly, you'll generate the error as well. Better to anticipate that possibility and put in the extra line of code to handle it.

Next Steps
  • Next time you have the need to create linked servers, but do not need to keep the linked server connection available at all times, try using this approach to help minimize any potential security issues between servers.
  • Review these others tips about linked servers
  • Review these other tips about SQL Server security


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2009-09-25

Comments For This Article




Wednesday, July 4, 2012 - 9:21:26 AM - Susheel Kumar Back To Top (18342)

Hello MsSqltips,..............

 

select * from linksserver...sheetname$ 

in the above sql suery

What is mean by [ ...  ]

 

Thnks and Regards

S.K.Verma

 

Monday, February 7, 2011 - 5:06:02 PM - ian Back To Top (12865)

Hi all, I know this is an old thread, but I'm trying to get some information - maybe if anyone's still seeing this thread they will have some advice for me?

For various reasons which are not particularly relevant here, I am interested in using this method to read excel files. Why not openrowset, you might ask? Well, I don't know the name of the worksheets, as they are user uploads from a web app. Anyway, I got this idea from a few posts and was able to make a stored proc that adds the linked server, queries the first worksheet, and then drops the linked server. It is fast and works great.

However, this may be hapening more than a hundred times per day; this just sounds like a bad idea to me, but I have no evidence of that whatsoever. Fragmentation? Excessive db growth? Other problems? (I'm taking your security concerns to heart, and checking for file exists, and so forth). I'd love to hear that  this solution is a viable one; it sounds bad on the face of it, but I don't really know, maybe it is just fine.

So, anyone with any knowledge and/or opinions on the matter care to offer some advice?

Thanks in advance,

Ian

 

Tuesday, October 6, 2009 - 8:36:53 PM - K. Brian Kelley Back To Top (4147)

[quote user="mharr"]

There are a couple of points that you do not mention that can catch users implementing your tip.

First, executing sp_addlinkedserver requires ALTER ANY LINKED SERVER permissions, which only the sysadmin or setupadmin server roles have by default. You will need to grant access to this permission for the user or application account that will be running the stored procedure (you don't run application accounts as sysadmin, do you?)

Second, by default, adding linked server adds permisisons for all logins on the server to access the linked server. Having a "temporary" like you plan certainly mitigates the security risk, but just as you check to make sure if the linked server is there in case it was not cleaned up last time, you probably should lock down access to just those logins that need it. You probably want to execute "sp_droplinkedsrvlogin , NULL" to drop all default logins, then explicit add your login with sp_addlinkedsrvlogin sproc (Of course, these sprocs require ALTER ANY LOGIN permission, which only securityadmin and sysadmin roles permit; will need to add this permission the user or application account.)

Third, the reader of this tip may want to do this for external file access, like linking to an Access database, Excel spreadsheet, or delimited text file. In these cases, it will be prudent to check if the desired file is there before attempting to create the link server. The undocumented extended procedure master.dbo.xp_fileexist (documented in previous tip http://www.mssqltips.com/tip.asp?tip=1272) is very handy for doing this.

Thanks for the handy tip.

[/quote]

 Yup, I didn't cover the detailed security side of things, because that's probably better suited for another tip. The idea was to give a quick solution like when you're running a job and need a temporary connection to another SQL Server.

 

Tuesday, October 6, 2009 - 8:35:22 PM - K. Brian Kelley Back To Top (4146)

[quote user="dooberry"]

So the answer is use a job to do it and run it when required?

[/quote]

 This provides a solution to use with a job, yes, if you don't want a linked server around all the time.

Friday, September 25, 2009 - 6:23:10 AM - mharr Back To Top (4093)

There are a couple of points that you do not mention that can catch users implementing your tip.

First, executing sp_addlinkedserver requires ALTER ANY LINKED SERVER permissions, which only the sysadmin or setupadmin server roles have by default. You will need to grant access to this permission for the user or application account that will be running the stored procedure (you don't run application accounts as sysadmin, do you?)

Second, by default, adding linked server adds permisisons for all logins on the server to access the linked server. Having a "temporary" like you plan certainly mitigates the security risk, but just as you check to make sure if the linked server is there in case it was not cleaned up last time, you probably should lock down access to just those logins that need it. You probably want to execute "sp_droplinkedsrvlogin , NULL" to drop all default logins, then explicit add your login with sp_addlinkedsrvlogin sproc (Of course, these sprocs require ALTER ANY LOGIN permission, which only securityadmin and sysadmin roles permit; will need to add this permission the user or application account.)

Third, the reader of this tip may want to do this for external file access, like linking to an Access database, Excel spreadsheet, or delimited text file. In these cases, it will be prudent to check if the desired file is there before attempting to create the link server. The undocumented extended procedure master.dbo.xp_fileexist (documented in previous tip http://www.mssqltips.com/tip.asp?tip=1272) is very handy for doing this.

Thanks for the handy tip.


Friday, September 25, 2009 - 6:18:06 AM - mharr Back To Top (4092)
There are a couple of points that you do not mention that can catch users implementing your tip. First, executing sp_addlinkedserver requires ALTER ANY LINKED SERVER permissions, which only the sysadmin or setupadmin server roles have by default. You will need to grant access to this permission for the user or application account that will be running the stored procedure (you don't run application accounts as sysadmin, do you?) Second, by default, adding linked server adds permisisons for all logins on the server to access the linked server. Having a "temporary" like you plan certainly mitigates the security risk, but just as you check to make sure if the linked server is there in case it was not cleaned up last time, you probably should lock down access to just those logins that need it. You probably want to execute "sp_droplinkedsrvlogin , NULL" to drop all default logins, then explicit add your login with sp_addlinkedsrvlogin sproc (Of course, these sprocs require ALTER ANY LOGIN permission, which only securityadmin and sysadmin roles permit; will need to add this permission the user or application account.) Third, the reader of this tip may want to do this for external file access, like linking to an Access database, Excel spreadsheet, or delimited text file. In these cases, it will be prudent to check if the desired file is there before attempting to create the link server. The undocumented extended procedure master.dbo.xp_fileexist (documented in previous tip http://www.mssqltips.com/tip.asp?tip=1272) is very handy for doing this. Thanks for the handy tip.

Friday, September 25, 2009 - 4:59:59 AM - admin Back To Top (4091)

You can just follow the steps in the tip you do not need to do anything else.

Basically before you can CREATE the stored procedure you just need to have the linked server in place so the stored procedure can be created.

Once the stored procedure has been created the linked server will be created and dropped when the stored procedure runs.

 

Friday, September 25, 2009 - 4:28:45 AM - dooberry Back To Top (4090)

So the answer is use a job to do it and run it when required?














get free sql tips
agree to terms