1

I have inherited a project that uses a local Microsoft SQL Server 2012 Express Edition and a central SQL Server 2012 as a backup.

There may be multiple local "machines"/databases that all are linked to the same server.

I have a script (query) that creates the linked server and the remote user.

Do I need to create a new user for each machine or can all machines use the save remote user?

The script looks like this:

USE [master]
GO
/****** Object:  LinkedServer [FOODB001]    Script Date: 2015-04-16 21:36:49 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'FOODB001',
                                   @srvproduct = N'SQL Server'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'FOODB001',
                                     @useself = N'False',
                                     @locallogin = NULL,
                                     @rmtuser = N'MyUser',
                                     @rmtpassword = 'password'    
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'collation compatible',
                                @optvalue = N'false'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'data access',
                                @optvalue = N'true'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'dist',
                                @optvalue = N'false'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'pub',
                                @optvalue = N'false'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'rpc',
                                @optvalue = N'true'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'rpc out',
                                @optvalue = N'true'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'sub',
                                @optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'connect timeout',
                                @optvalue = N'0'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'collation name',
                                @optvalue = NULL
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'lazy schema validation',
                                @optvalue = N'false'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'query timeout',
                                @optvalue = N'0'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'use remote collation',
                                @optvalue = N'true'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'remote proc transaction promotion',
                                @optvalue = N'true'
GO
2

The technical answer

To my knowledge, there's no problem in using the same user for all "client" machines. The same user account can connect to a SQL Server as many times as the licensing of that SQL Server allows.

The security perspective

However, you may want to review the security aspects of this setup - do all the remote machines have the same access requirements, or should they have different users for security reasons?

Could you perhaps even use windows/AD authentication? Remember that when you hard-code an account into the linked server, pretty much anyone who can log on to the remote server will gain the privileges on the "central" server that you've given to the user account defined on the linked server, which may lead to a privilege escalation.

Tread carefully with linked servers and permissions, is all I'm saying.

  • I was just worried that the same user could not be logged in from multiple databases at the same time. The overall setup is done by an external consults. I just want to survive a new machine installation (for now). Maybe later I will look what should be "fixed" Thank you for your answer! – Andis59 Feb 3 '16 at 9:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.