Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Posted Dec 5, 2003

Linked Severs on MS SQL Part 3

By Don Schlichting


In Parts 1 and 2, we introduced the Linked Server. Starting with what a Linked Server is, the benefits to using them, as well as when to avoid them. The Distributed Transaction Coordinator, DTC, was touched on. In addition, our first examples were to create links to an Excel spreadsheet, during which, four-part naming was reviewed. The functions sp_addlinkedserver, openrowset, and sp_dropserver were used. Moreover, the differences between Direct Referencing and using the openrowset were explained. The final examples were to link an Access database, and a SQL database using Windows security. In this third part, we will continue with linked SQL server security.

Windows Security

Let's create a Linked SQL Server with standard Windows security as a starting point. Open Query Analyzer and connect to your local SQL with Windows authentication.

From inside Query Analyzer, we will use the sp_AddLinkedServer function to create our link:

EXEC sp_addlinkedserver
	@server = 'TEST1',
	@srvproduct = 'SQLServer OLEDB Provider',
	@provider = 'SQLOLEDB',
	@datasrc = 'InfoNet'

@server is the Link name. @datasrc is the remote SQL server.

As a test, run a simple select out of our linked server:

FROM TEST1.Northwind.dbo.Orders

The full orders table should be returned. If not, verify you have adequate permissions on the remote machine. Then check for type errors in addlinkedserver statement.

Alternatively, to create the link from inside Enterprise Manager, navigate your local server tree to Security, Linked Servers, right click, and select "New Linked Server." Select SQL Server as the server type, and use the Linked Server box for the remote server name. In this case, the remote server name will also be the Linked Server name.

The connection just made will pass your Windows login credentials to the remote server. If the Windows account does not have SQL permissions on the remote, all queries will fail. Open the security properties for the link we just created. From Enterprise Manager> Security> Linked Servers, right click on the TEST1 link. The center maps local logins to others on the remote. These must be explicitly created. Any logins not found here, or if it is empty, as in our case, then the radio buttons on the bottom take over.


To create the same security context from Query Analyzer, use the sp_addLinkedSrvLogin function. This function handles the same basic options as Enterprise Manager. The syntax is straightforward:

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
    [ , [ @useself = ] 'useself']
[ , [ @locallogin = ] 'locallogin']
    [ , [ @rmtuser = ] 'rmtuser']
[ , [ @rmtpassword = ] 'rmtpassword' ]

@rmtsrvname - is the linked server name previously created.
@useself - if set to true, Windows authentication is used. False will use SQL security.
@locallogin - Local SQL user ID.
@rmtuser - Remote SQL user ID.
@rmtpassword - Remote SQL password.

For the above example, where Windows security was used exclusively, the syntax is:

EXEC sp_addlinkedsrvlogin 'TEST1', 'true'

Not be made

If the "Not be made" radio button is selected, then any logins not explicitly mapped will be denied. In our case, with no logins defined, all queries will fail. The test sql statement:

FROM TEST1.Northwind.dbo.Orders

Will fail with error:

Server: Msg 7416, Level 16, State 1, Line 1

Access to the remote server is denied because no login-mapping exists.

Be made without using a security context

When the "Be made without using a security context" is selected, any queries submitted by a login not defined will be passed to the remote server using guest permissions.

From Query Analyzer, the security syntax is:

EXEC sp_addlinkedsrvlogin 'TEST1', 'false', NULL, NULL, NULL

Be made using the login's current security context

This has been the only permission level used so far. Queries submitted by a login not explicitly mapped are passed with the current Windows login security. The Windows account must have permissions on both the local and remote SQL databases.

From Query Analyzer, the security syntax is:

EXEC sp_addlinkedsrvlogin 'TEST1', 'true'

Be made using this security context

Selecting this security context maps any logins not explicitly defined to one specific SQL account on the remote. The password entered here will not automatically synchronize password changes with the remote. Any password changes made on the remote must be manually changed here as well.

From Query Analyzer, the security syntax is:

EXEC sp_addlinkedsrvlogin 'TEST1', 'false', 'userLocal', 'userRemote', 'remotePassword'

To specify a local Windows login, rather than a SQL login, use the keyword word "domain\".

EXEC sp_addlinkedsrvlogin 'TEST1', 'false', 'domain\localWinUser', 'userRemote', 'remotePassword'