September 8, 2006 at 3:06 am
Background: We have a customer who will be distributing the SQL2005 + VB.Net system we're developing.
The system is made up of a comms hub that communicates with upto 30 clients over TCPIP, the comms hub uses SProcs in the DB to process the messages and also to maintain internal models of the state of the 30 clients. The front end is a realtime view of the internal models of those clients and their interactions and an interface to enable various commands to be sent to the clients. The front end only connects to the DB not the comms hub, but communicates to the comms hub via the DB by writing to a table which has query notification subscriptions in the comms hub. When the comms hub receives a notif event from the DB it retrieves and processes the command using a SProc and carries out any required messaging with the comms clients.
We've built the security side of the system to use trusted connections (windows auth) from the front ends to the DB using a primary domain controller (active directory) on the DB server machine. i.e. the system runs within a domain.
Our customer is pushing us into allowing them to distribute the system using a workgroup but still windows auth. We thought this wasn't possible, but it seems that if the same local windows user is created on the server and client machines with the same pwd that trusted connection does work. However on the client and server machines the user must be created as a local administrator and that means the front end user gets sysadmin privileges within the DBs. No user/login is required to be created in the DB/DB server itself.
I need some advice on how to persuade them not to go down this approach, so please help if you can!!!
Ideally something which says this is bad practice from a security and/or design would be great!
September 8, 2006 at 6:46 am
Rob
I agree that it does seem a daft way of doing, but you don't need to have everybody having sysadmin access. Just delete the BUILTIN\ADMINISTRATORS user (or whatever it's called under SQL Server 2005) and set up a Windows login on the SQL Server based on the Windows login the users will be using. You can then assign whatever access you like to that user.
You might want to mention to your customer that when the password needs changing (and it will) it will have to be done individually and at the same time on every computer. If they don't want to use Windows authentication on a domain, it's better to use SQL Server authentication.
John
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply