3

I am a beginner with SQL so I struggle with the MSDN description for creating a linked server in Management Studio.

I whant to link a SQL Server into another to use everything from ServerB on ServerA to e.g. provide one location other systems can connect to.

Both servers are in the same domain and both server have several databases inside.

When I start creating a linked server on ServerA in the general tap I select a name for the linked server and select SQL Server as Server type. But I struggle on the Security tap. I have on both servers sa privilege so what is to set here? Or which role should I take/crate for this connection?

My plan is to create views in a certain DB on ServerA with has also content of ServerB inside. This views will be conusumed from an certain AD service user. I already added this service user to the security on ServerA where the views are stored. Do I also have to add this user somewhere on the linked ServerB?

Improve this question
4
  • Are you using Windows Integrated Security for authentication or do you login to your servers using the SQL server sa account? Commented Jun 6, 2014 at 7:52
  • Sorry that I forgot this detail! Yes we use Windows Integrated Security. Commented Jun 6, 2014 at 8:00
  • My plan is to create views in a certain DB on ServerA with has also content of ServerB inside. This views will be conusumed from an certain AD service user. I already added this service user to the security on ServerA where the views are stored. Do I also have to add this user somewhere on the linked ServerB? Commented Jun 6, 2014 at 8:11
  • 2
    You might be able to find some useful questions/answers on Database Administrators StackExchange as well. Commented Jun 6, 2014 at 8:25

3 Answers 3

Reset to default
2

I would recommend that you use Windows Authentication. Activate Security Delegation.

In the Security tab, choose "Add". Select your Windows user and check "Impersonate".

As a quick and dirty solution, you can choose "Be made using this security context" from the options list and enter a SQL Login which is valid on the remote server. Since quick and dirty solutions tend to last, i would strongly recommend to spend some time on impersonation.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I do not whant a dirty solution! I like to have the correct and clean solution :)
1
  1. In Server Objects => right click New Linked Server

enter image description here

2. The “New Linked Server” Dialog appears. (see below).enter image description here

3. For “Server Type” make sure “Other Data Source” is selected. (The SQL Server option will force you to specify the literal SQL Server Name)

  • Type in a friendly name that describes your linked server (without spaces). – Select “Microsoft OLE DB Provider for SQL Server”
  • Product Name – type: SQLSERVER (with no spaces)
  • Datasource – type the actual server name, and instance name using this convention: SERVERNAMEINSTANCENAME
  • ProviderString – Blank
  • Catalog – Optional (If entered use the default database you will be using)

  • Prior to exiting, continue to the next section (defining security) enter image description here

  • Click OK, and the new linked server is created

Improve this answer

Comments

1

The easiest way would be to specify to make the connection using the current context, i.e. the user loging on to ServerA:

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

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ServerB',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

This however assumes that users accessing the linked server connection have the correct privileges on ServerB (part 2 of your question) - otherwise you'd need to use impersonation as described by Martin K.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.