June 29, 2014 at 10:15 am
We have a database that houses student data for 20+ school districts. The designers opted to put all of the districts into a single database as opposed to creating a separate database for each district. This creates a security issue when the districts want to download data for various projects.
To date, this ability has been handled using two servers: D01 and RPT. The servers are linked.
D01 contains the database (DASL) with all of the student data, and another database (DASLr2) with views that contain security to limit what each user has access to.
RPT contains a database (DASLr2) that has simple views that connect to the views on D01 to retrieve the data. A view would be something like "select * from D01.DASLr2.dbo.Courses".
The district users have SQL accounts on RPT. A linked server login is used to connect to the D01 server to retrieve the data. This prevents the district users from having access to the DASL database. This type of security only works with SQL 2008 and earlier using two separate servers. This could not be accomplished on a single server.
I thought I recalled reading a couple of years ago where SQL 2012 could do this within one server. I've searched for a few hours on the web and this site, but haven't found a solution.
Any ideas?
Thanks!
June 30, 2014 at 7:40 am
Hmm.
I'll take a very poor stab at this to give those that know more something to criticize!
First, set up user security:
1) Set up the linked server to pass along the original security, i.e. "be made using the login's security context" - though I've only ever used this with AD users (Windows authentication), and I think that this only works with AD users, so you'd have to change how users connect.
1a) Probably, you'll need to set up the AD users on both servers.
OR
2) Set up the linked server with individual impersonation for each user, source user01 to target user01 with that user's password
2a) Set up unique SQL Server users for every school on both servers, so user01 on the login server maps to user01 on the target server, etc.
-- Add specific logins
-- NOTE that if proxy users aren't enabled, the SQL Server Agent "sa" login will be the Service Account for the SQL Server Agent.
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LinkedServerName', @locallogin = N'LocalUserAllowedToUseLinkedServerWithRemoteSQLAuth', @useself = N'False', @rmtuser = N'RemoteServerUserName', @rmtpassword = N'RemoteServerPassword'
-- WARNING: AD Groups are NOT ALLOWED - only AD Users are allowed (at least on SQL Server 2005 SP4)
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LinkedServerName',@useself=N'True',@locallogin=N'LocalUserAllowedToUseLinkedServerWithTrustedAuth',@rmtuser=NULL,@rmtpassword=NULL
-- remove the default login to be sure it's not present.
EXEC master.dbo.sp_droplinkedsrvlogin @rmtsrvname = N'LinkedServerName', @locallogin = NULL
OR
3) Consolidates the data somewhere and thus eliminates the need for linked servers.
Then, regardless, you need to verify restricted data access - you've got your views in a database already, but you need to log in with the credentials of one of these users (i.e. create a test user just the same way) and then try:
A) To access other school views
B) To force a view to provide data on another school
C) To update the view and alter data on another school
C1) To "move" data from the school that the user should have access to into a school they should not, i.e. verify the CREATE VIEW WITH CHECK OPTION statement
D) To access the database containing the source tables, and then to directly access the source tables the view is accessing without using the view at all
E) be creative! SQL Injection via stored procedure with dynamic SQL! Check for ##GlobalTempTables! Permanent tables in tempdb! etc. etc.
P.S. If you use hardcoded @rmtuser and @rmtpassword, you need to turn on and verify (sys.dm_exec_connections, and then ideally a packet sniffer) that you have SQL Server connection encryption enabled, otherwise those credentials are almost certainly being sent in cleartext and anyone on either server or anywhere on the path the packets travel over can trivially obtain those credentials.
July 1, 2014 at 6:57 am
This link shows how to create a loopback linked server http://www.kodyaz.com/sql-server-2012/create-local-linked-server-in-sql-server.aspx
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply