Sql-server – Do I need different linked server remote users

linked-serversql serversql-server-2012

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

Best Answer

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.

Related Solutions

Sql-server – SQL Server 2008 linked server to an Access 2007 DB that is in a protected directory on the Network

I'll just summarize all my comments here as an answer.

You should read this:

http://msdn.microsoft.com/en-us/library/ms143504%28v=sql.105%29.aspx#Use_startup_accounts

The SQL Server Service is the SQL Server engine and runs under an account specified for the service and linked servers which use a file share will necessarily use those permissions, since there is no setting to have the Server Service impersonate another account when it connects through the filesystem.

The SQL Server Agent is a separate process which runs scheduled jobs. If you use this and it connects to network shares on its own (not just telling SQL Server engine to run some statement against a linked server which would be under the engine permissions above), it would need to run under an appropriate domain account. In your case, that doesn't appear to be running, so I wouldn't bother fooling with it.

Running the SQL Server Express has certain database size and functionality limitations, and I wouldn't consider it for general server-class production use except for high numbers of lightweight servers - for instance a deployment of an small inhouse application to several worksites - where you wouldn't want to pay for licensing and the requirements fit the product.

The built in network account is "NT AUTHORITY\NETWORK SERVICE" and you can just put that in and blank out the passwords to restore it.

If you switch the SQL Server Service to run as a domain account, you will need to give it sufficient permissions to access the OS on the database server - i.e. folder access to where the database files are if the account doesn't have access.

Sql-server – Run Multiple Remote Jobs

I'd say the easiest possible way to do this is through PowerShell and SMO. Take the following code for instance:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |
    Out-Null

$SqlServerNames = "Server1", "Server2"
$SqlJobName = "YourJob"

foreach ($SqlServerName in $SqlServerNames) {
    $SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)

    $SqlServer.JobServer.Jobs[$SqlJobName].Start()
}

All this does it loop through a list of servers (explicitly stated at as string array variable, but can easily be obtained also from a SQL Server database table, a text file, etc.), connects to the current server, and then starts the job with the job named whatever you set the $SqlJobName variable to.

It's also worth noting that the Start() function will not wait for the job to complete before continuing code execution.

As you can see, in very few lines of PowerShell code you are able to accomplish this task. But I'd take it a step further, such as error handling. You don't want this to bomb out and not know what failed, when it failed, and what servers it did/didn't run on.

foreach ($SqlServerName in $SqlServerNames) {
    try {
        $SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)

        $SqlServer.JobServer.Jobs[$SqlJobName].Start()
    }
    catch {
        # add your error handling code here
        ## for instance, write the error to a text file
    }
}

And, as always with any code, test this out in a non-production environment to ensure that it does what you think it will and should do. Re-work it for your needs/environment.

As per RoKa's comment:

Can this be edited to check for whether the job is currently running, error handling?

Great point, and definitely something to check for (as well as if the job actually exists by checking for a non-null value):

foreach ($SqlServerName in $SqlServerNames) {
    try {
        $SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)

        $SqlJob = $SqlServer.JobServer.Jobs[$SqlJobName]

        if (($SqlJob) -and ($SqlJob.CurrentRunStatus -eq [Microsoft.SqlServer.Management.Smo.Agent.JobExecutionStatus]::Idle)) {
            $SqlJob.Start()
        }

        else {
            # handle/log accordingly if the job doesn't exist or it's not idle
        }

    }
    catch {
        # add your error handling code here
        ## for instance, write the error to a text file
    }
}

EDIT: While re-reading your question, I see SQL Server 2000. Are those going to be the target instances? If that is the case, and you do decide to go with the PowerShell/SMO answer I provided, then definitely definitely definitely test and ensure it is going to do what you want. Test this out very far from production and be 100% sure it does what you think it will.

EDIT: It looks like SMO versions before SQL Server 2012 (i.e. 2005, 2008, and 2008 R2) support managing SQL Server 2000 instances. I'm finding this information through this BOL reference.

Best Answer

Related Solutions

Sql-server – SQL Server 2008 linked server to an Access 2007 DB that is in a protected directory on the Network

Sql-server – Run Multiple Remote Jobs

Related Question