We are looking at setting up a linked server from one SQL Server to another. We will create the linked server on the primary Database. And it will link to a read-only secondary server - i.e. the server it's linking to is in high availability, replicating from the primary.
The idea put forth was to make a linked server for each Database rather than creating just one. Does anyone find any advantage to that? The rational was to ensure that we can't access the wrong Database i.e. if it's called on the Primary Server as Customer A, it can't retrieve information from Customer B's Database, in the case that there is an error in the Stored Proc. The thing is we already have security in place on each server to prevent this. So I thought that if "Be made using the logins current security context" is chosen, we would get a permission error instead, in case the Stored Procedure is trying to access the wrong Database.
Linked server as the name implies is server based not db based
In your case the best option might be to create a linked server per customer using one account per customer. Five that specific account only the necessary permissions. but depends how many customers you have. How many are there?
I'm just trying to understand this regarding having a separate linked server for each customer (as that was the idea put forth)... UserCust1 has access to DatabaseCust1. And UserCust1 does not have access to DatabaseCust2. So wouldn't that mean the linked server would also follow this provided the linked server's Security option "Be made using the logins current security context" is chosen?
do you know the login they are coming as? ("Be made using the logins current security context") and based on that what decisions are you making in your code?
The Login for Cust1 would be UserCust1 etc... So if we went with the one linked server, the code in DatabaseCust1 would be: select * from LinkedServer.DatabaseCust1.dbo.Table1
And if by mistake that code was select * from LinkedServer.DatabaseCust2.dbo.Table1
... I understood they'd get a permission error. But that's what I'm trying to double-check. If so, then I'm trying to understand the need for having more than one Linked Server.
At the moment, our Windows Domain set up won't allow us to set up the linked server using "Be made using the logins current security context". We have that "double-hop" issue. As soon as that is sorted out, I can give this a try. I'm just trying to learn ahead of that.
Each customer has their own Windows Domain Account. But the customer doesn't directly touch these. It's all via the web. So when a customer logs in, the webserver connects to SQL using that Customer's Windows Domain Account.
Same web application. Different URLs. Each customer has their own version of their website. Some personal information such as employee names. Separate Databases with the same table structure and same stored procedures.
Do they all share the same apppool?
And why not set the db configuration on IIS to point each vendor to their unique database.
In other words keep things nice and clean by using IIS configuration. Run the app pool for each web application using a different app pool for each vendor.
Yes, each customer already, via the configuration goes to the correct Database, using the correct user. As I mentioned the Linked Server would link to a read-only secondary server - i.e. the server it's linking to is in high availability, replicating from the primary. The reason of course is to prevent read locks by going to the read-only secondary. Another option of course is Snapshot Isolation but the app wasn't written with that.
If it sounds confusing why we would want a separate linked server per database, well that sounds confusing to me as well, like I mentioned, it wasn't my idea. That's why I'm posting, to see if anyone knows/can think of a good reason. The rationale I was given, which I wrote above, didn't make any sense to me.
Yeah, if we have to add another customer, then it would also require yet another linked server. Hopefully I can get more information on this idea, or maybe our dept. will agree it's not the best approach...
What concerns me the most about this is that you are considering setting up a linked server on the primary node that is configured to access the secondary for databases hosted on that same primary node.
If the goal is to prevent blocking on the primary databases - there are much better alternatives. If the goal is to offload the processing to the secondary, then using a linked server isn't going to resolve that problem either (the primary has to pull the data across from the secondary into the buffer cache on the primary - and most likely will spill to tempdb).
The ideal solution would be to move the work over to the secondary completely - that way you avoid blocking and performance related issues and security takes care of who can see and access which databases.
