November 28, 2007 at 7:33 am
I am designing a desktop application that the user will run locally on their machine with Sql Server Express on the user's machine. I am concerned about starting sql server and leaving it running when the application is not in use (leaving the database open and running could create a security hole, no?). Is this a legitimate concern?
If so, what is the best way to start and stop sql server from code? Is this a best practice, or can you recommend a better way to mitigate security risks? I am basically looking for suggestions or links to references that would help me design a better system.
Thank you.
November 28, 2007 at 7:57 am
Well - considering it's on their machine - I'm not sure how much of a security hole you might be worried about. After all - if the workstation isn't locked down, then someone can just swipe the DB files, and well - do anything they want to them: break the security, get in, etc...
I'd think you'd be better off leaving the instance running, and concentrate on making some good choices about security inside the database. Try setting the server to NOT respond to TCPIP or named pipes, so that remote connections are not allowed. You should still be able to use Native SQL client connections against it in shared memory AFAIK, and then lock down the other workstation "holes" (sharing/remote desktop/RCP).
That being said - there's a way to set up "user instances of DB's" using .NET that load the DB only when the app requests it. You don't attach the DB, you simply include it, and your app attaches it. As a matter of fact - that tends to be the default way .NET sets up "new" database connections (if you tell it you want a "new" database during the wizard.) You can also set your DB to auto-close (meaning you attach it, but set it to auto-close).
I don't much like either scenario though, since you run into a delay each time you "first" hit the DB. The overhead for loading the DB is pretty intense. Not only that - but it's hard to schedule something to run against a DB that isn't attached....
Finally - you can always use the command prompts NET START to start and stop the service, or there is a WMI syntax to do that as well. Again - I'd leave all of that alone, and just keep SQL running and make sure it's locked down on the inside. That will be the biggest impediment of all.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 28, 2007 at 8:05 am
Matt,
Thank you for your prompt and well written reply. For those who may be unfamiliar with connecting to SQL Server with Native Connections (I'm most experienced going over TCPIP), here is a link to some sample code from the MSDN: http://msdn2.microsoft.com/en-us/library/ms130978.aspx.
Again, kudos and many thanks.
Ben
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply