I am not sure how to ask this question. We have an internal server "123" that we allow a Vendor "A" to access. This vendor needs access to data from Vendor "B" but they don't have the credentials to get to Vendor "B". I am planning to setup a Link Server on server "123" to access Vendor "B" data. However, I realized that they would have access to all of Vendor "B" data unless I build a view.
My question is, as soon as I setup a Link Server object on server "123" to access Vendor "B" data, Vendor "A" would have access to the entire Vendor "B" database right?
No. Create them their own linked server, and use a remote login on that (logical) server name that has access to only what you want that vendor to see.
Remember that the linked server name does not have to be the same as the actual server name (in fact it should not be anyway, but that's a separate issue). In this specific case, you need to use a different name for security reasons.
So create a "VendorA_Link_To_VendorB" server. Then create a specific, probably local (native SQL) login that has only the permissions that VendorA needs for VendorB's data, and use that as the login to get to the other server. You could even limit that linked server to only being used by VendorA logins if you wanted to.
I think I get what you are saying Scott. Before I reiterate, let me add one more note that I just found out. Vendor A has full access to our server which is at SA level and I believe at Local Admin. I am not sure why they have it that level, usually, should only be at the database level. This means that if I setup a link server to Vendor B data, the link server should be setup on a DIFFERENT server right? Like server "456" instead of server "123" which Vendor A has access to.
Again, we would like Vendor A to see only a subset of data from Vendor B and for that reason, a view will be created. Thanks
the link server should be setup on a DIFFERENT server right?
No, not really.
The linked server name does not have to be an actual instance name.
People get in the habit of naming the linked server exactly what the original server name is. They accept the default way SQL Server does it. But that is NOT required and is in fact a bad idea.
Instead of using the default linked "Server type:" as "SQL Server", use "Other data source" and use different names for the "Linked server:" name (the logical server name on the linking from server/instance) and the "Data source:" (the actual SQL instance name).
1st create a SQL Server user in Vendor B and give it the necessary perms to the view only in Vendor B
- sql_user_name
- sql_user_password
I think the rest here below is straight forward? test it out, maybe even add some logging to see what they are looking at. any confidential data, hippa etc?
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'VendorA_Link_To_VendorB',
@srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'internal_server_123'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'VendorA_Link_To_VendorB',
@useself=N'False',@locallogin=NULL,@rmtuser=N'sql_user_name',
@rmtpassword='sql_user_password'
GO
EXEC master.dbo.sp_serveroption @server=N'VendorA_Link_To_VendorB',
@optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'VendorA_Link_To_VendorB',
@optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'VendorA_Link_To_VendorB',
@optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'VendorA_Link_To_VendorB',
@optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'VendorA_Link_To_VendorB',
@optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'VendorA_Link_To_VendorB',
@optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'VendorA_Link_To_VendorB',
@optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'VendorA_Link_To_VendorB',
@optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'VendorA_Link_To_VendorB',
@optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'VendorA_Link_To_VendorB',
@optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'VendorA_Link_To_VendorB',
@optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'VendorA_Link_To_VendorB',
@optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'VendorA_Link_To_VendorB',
@optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
Scott, I think I may have not explained it clearly enough. So let me step back a bit. Vendor A is trying to access to Vendor B data but we can only give Vendor A a subset (in a view) of Vendor B data. Vendor A is Local Admin and SA for server "123". If I setup a Link Server on that server "123", that would be a security issue right because Vendor A would be able to access ALL data from Vendor B using the created Link Server on server "123". We only want Vendor A to see a subset of data in a view from Vendor B.
That looks reasonable. Just be sure that the "sql_user_name" login/user on "the internal_server_123" server has the appropriate permissions.
Typically you'll want to enable "rpc" or "rpc out", but you may not in this specific situation.
I think you got it for the exception that Vendor B is a vendor and therefore, we only have read access to their database. I would have to create a link server to Vendor B on our internal staging server then another link server from Vendor A server "123" to our staging server.
I think what you are missing is that creating a linked server does not automatically grant 'full' access to 'everything' in the linked server. You control and manage the security...which can be setup so that the logged in user on 123 is mapped to a specific user on the other server - and that login only has access to what is required.
The only way Vendor A would be able to escalate that access - is if Vendor A has access to the username/password on Vendor B's system with that access. Or - you create the linked server between the systems and map a user/login to a privileged account on the other server (a sysadmin can EXECUTE AS to impersonate the local user that is mapped to the more privileged account).
In this situation - I would probably build an extract process instead of a linked server. That way I control what is extracted and therefore control what data is shared.
1 Like
To add to that the extraction could be to a file or even a dedicated locked down database somewhere else other than main db?
OK, I got the Link Server setup.
I have another question. Is it possible to query across servers (INTERNAL Servers) without a Link Server?
You can use OPENQUERY or OPENROWSET, but it's actually much easier to set up a linked server.
Thanks Scott. I thought there might be a way to query across server but I doubt it since there is the subject of security.
I agree the other option is what you have provided or Link Server.
I guess my question would be... does Vendor "B" know that their data is going to be used by a different vendor? If so, do you have it in writing?
Technically, Vendor B is only housing our data. We actually own it. The company has bought a software platform from Vendor B for about N million. When our physicians and nurses use the system, transactions get saved to Vendor B servers.
We only have read access to Vendor B data source (report database) and therefore, we cannot create a new SQL Authentication user on that server/database to control permission. For this reason, we pull the data back to our staging area (currently a Link Server but eventually an SSIS package) and created a SQL Authentication user, setting the security as recommended from previous posts.
We then setup a Link Server on Vendor A to the staging area server with the above created SQL Authentication user accessing only selected data from Vendor B through a view (sitting on Staging server).
We cannot allow Vendor A to connect directly to Vendor B because that would expose all Vendor B data. There are two Link Server setups. One between Vendor A to Staging Server. Second one between Staging Server to Vendor B (Read Only permission).