Security Concern using Linked Server in SQL Server to production database

Question

Hi,
My name is Martin. Recently, I have a project to build a process to pull production data from a SQL Server host (say Prod) to a different SQL Server host (say, Pub). This Pub database server will eventually be moved to our DMZ for public web access. In order to run the process, I have created an insert sp in the Pub database, and have configured the Pub SQL Server under the the Linked Server to link to Prod so that the process will directly insert the data to the Pub from Prod.
My question is, with the Pub server with linking to Prod in the Linked Object in SQL Server, what is your opinion on this from a security point of view, the prods and cons if I am ask, or any sources I could be referred to go for further research in this area is highly appreciated.
Thank you.
Martin

Answer 1

Hi @Martin Ng ,

Quote from this MS document;

Linked servers can also be created using the sp_addlinkedserver stored procedure. There are security considerations associated with this operation. When a linked server is created using sp_addlinkedserver, all local logins will be mapped to the new linked server by default. To control access to the linked server, the sp_droplinkedsvrlogin procedure should be used to drop the global login mapping, followed by sp_addlinkedsvrlogin to map the desired login account(s) to the new linked server. When using sp_addlinkedsvrlogin, it is recommended that you set the @useself parameter = TRUE. This avoids the need to embed a user name and password into your SQL script.

Suggest you starting from below blogs to get more about linked server.

How to create and configure a linked server in SQL Server Management Studio
Linked Server in a SQL Server: the good, the bad, and the way to make it perfect!

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Note that the document that Cathy quoted relates to BizTalk, which is maybe not the most authoritative source for SQL Server security. I do certainly not agree with what is suggested in the document.

If you set up a linked server with sp_addlinkedserver (as you should), the default is self-mapping. That is, when a user on the local server access the remote server, the user will be authenticated as if the user had connected directly to the remote server. That is, if the user does not have an account on the remote server, authentication will fail.

On the other hand, if you set up login mapping, you have to be very careful. You can either do that for everyone or for specific users. If you do it for everyone, everyone on the local server can indeed access the remote server using that login. This can be really serious if the account has access to sensitive data.

Now, in this particular case, I think your problems will be different. It is not clear to me that the prod server will have access to the pub server, since the pub server will be in DMZ. And even if it has, Windows authentication and self-mapping may not work. Particularly not if you log on from your own machine and connect to the pub server. This would require you to set up login-mapping, but as I discussed that is a dubious from a security perspective.

Answer 3

Thanks ErlandSommarskog for adding your comments.
Security is very tricky and is in a very sensitive area, I welcome and open discussions as always.
I have noticed Cathjhi's the second source of reference on 'the good, the bad...', and has also factored this in as part of my researching effort, and thanks for your input also.
The prime idea of having Pub database in DMZ is to separate the sensitive data from Prod and only keeping non-sensitive data to be accessible by public in the DMZ. So my process is to extract those non sensitive data from Prod to Pub, so that only non sensitive data is physical stored in Pub for public access in the DMZ.
I believe what has happened after I've finished building the process, having the link server configuration in the Pub linking to the Prod could have got a red flag in the vulnerability scanning report.
My manager has brought it up in the meeting that what if the Pub was hacked in the DMZ, and it has an existing live linking to the Prod, even those it does not have the Prod data..... I am kind of speechless.
So, I need to take out the link configuration in Pub for now, and try a different approach. But I will need to continue to research on it to see if there is a common ground or best practice I could quote running this type of project with database server in the DMZ.
Note: I believe I have done what Cathjhi's has suggested. And if I understood yours correctly, I did yours too. Here is a masked section of my script creating the link is Pub.
EXEC master.dbo.sp_addlinkedserver @Testta = Nxxxx', @srvproduct=N'SQLSERVER', @provider=N'SQLNCLI11', @datasrc=N'xxxx';
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'xxx',@useself=N'False',@locallogin=NULL,@X ,@xxxxxxxxxxxxx ';
EXEC master.dbo.sp_serveroption @Testta =N'xxxx', @optname=N'data access', @optvalue=N'true';
EXEC master.dbo.sp_serveroption @Testta =N'xxxx', @optname=N'rpc', @optvalue=N'true';
EXEC master.dbo.sp_serveroption @Testta =N'xxxx', @optname=N'rpc out', @optvalue=N'true';

Thanks all.

Answer 4

Here is a masked section of my script creating the link is Pub.
EXEC master.dbo.sp_addlinkedserver @Testta = Nxxxx', > @srvproduct=N'SQLSERVER', @provider=N'SQLNCLI11', @datasrc=N'xxxx';
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'xxx',@useself=N'False',@locallogin=NULL,@X ,@xxxxxxxxxxxxx ';

That's absolutely a red flag for this! This means that anyone who gains access to Pub can access Prod through this linked server. I would be a different thing if you set @locallogin to a specific user, so that only that user can access the linked server. And that must not be the application login for the pub environment.

From a security perspective, it would be more passable if the linked server was in Prod over to Pub. Then again, I wrote my reply yesterday under the impression that this was the case, since I did not read your post carefully enough... Anyway, often leads too other problems when trying to insert or update on the remote server.

I think you should consider a program that reads the data from Prod and then writes to Pub. I guess many people would use SSIS, although I don't know SSIS myself. If it is straight copy without transformation, you could use BCP to move the data.