Creating a linked server in SQL Server 2008 is a great way to
run CRUD statements against a completely different remote server. This method
relies on Windows Authentication.
This is only one way to do it.
There are others. And, if you are going to run SQL statements against a linked
server from a web app, you need to have the permissions set up properly. From
the article “How do I… Query foreign data using SQL Server’s linked
servers?” by Susan Harkins:
The biggest catch to all this simplicity is, as always, security. If you’re working with Windows Authentication, the system accommodates well. If the user has the appropriate permissions for the servers and data sources, linked queries will work.
If you have users outside the Windows Authentication
environment, you can use a linked login. Create a standard login and assign the
appropriate permissions on the remote server.
Setting It Up
1.
Go to “Server Objects” of the database server where the linked
server will be added and right click on “Linked Servers”. Select “Add New Linked
Server”.
2.
In the “General” tab, add a name for the new linked server in
the “Linked Server” field.
3.
Select “Other data source”and for “Provider” select “Microsoft
OLE DB for SQL Server”
4.
For “Product Name” type in “SQLOLEDB”
5.
In the “Data Source” field, enter the IP address of the server
to be linked
6.
“Catalog” is the name of the database on the linked server and
is optional
7.
Go to the “Security” tab and select “Be made using this security
context”. Type in the remote login and credentials
Running Queries
To query against this server user the syntax:
SELECT * FROM [MY_LINKED_SERVER].[database_name].[dbo].[table_name]
|
Or, for a join, use linked table with an alias (example):
SELECT * FROM Table
|
|
RIGHT OUTER JOIN
|
|
[MY_LINKED_SERVER].[database_name].[dbo].[table_name]
LinkedTable
|
|
ON Table.column = LinkedTable.column
|
|
Reference:
No comments:
Post a Comment