October 26, 2009 at 7:45 am
Hi,
I have a SQL Server 2005 Express Edition installed on a PC, with our homemade production control software. We need a very secure environment, no one should ever access the database except us. How can I denied the current Windows user to load up SSMS and view the database? The PC will of course be login, for our software to run the production line, but the only connection to the database should be the software or an administrator from our company.
How can I do that?
thanks a lot for your time and help
October 26, 2009 at 7:52 am
Is the pc on a domain? If so, you can control the security quite easily.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 26, 2009 at 8:44 am
thanks for the reply.
no, the PC isn't on a domain. It's a standalone computer with no network connection outside the production line.
October 26, 2009 at 9:01 am
You cannot do this. If someone is an administrator on the machine, and they have control of the environment, they can possibly get in.
You can make it more difficult. You can set a complicated SA password, and enable SQL auth. Then remove all the sysadmins from windows groups (usually just builtin/administrators). In this way the casual user will not be able to access the SQL Server.
October 26, 2009 at 9:23 am
Dominic Gagné (10/26/2009)
Hi,I have a SQL Server 2005 Express Edition installed on a PC, with our homemade production control software. We need a very secure environment, no one should ever access the database except us. How can I denied the current Windows user to load up SSMS and view the database? The PC will of course be login, for our software to run the production line, but the only connection to the database should be the software or an administrator from our company.
Did you install SQL Server Management Studio or SQL Server Management Studio Express on the PC?
K. Brian Kelley
@kbriankelley
October 26, 2009 at 11:42 am
SSMS Express is the one installed. Does it makes a difference regarding the security?
October 26, 2009 at 11:49 am
How are you loging to SQL Server now? I guess you must be using SQL Login.
You cannot disable Windows Authentication completely but rather the workaround way is you Deny Login to all the Windows User Account.
Before you jump on and do it, check that you have atleast another SQL Server Login that has Sysadmin Server Role permissions (Incase you need to login to the server and you have additional SQL Login with sysadmin Server Role)
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 26, 2009 at 12:40 pm
Dominic Gagné (10/26/2009)
SSMS Express is the one installed. Does it makes a difference regarding the security?
Realize that as an administrator I can get in and you can't do anything about it. Let's start with that as a foundational understanding of the situation. That's the nature of the beast whether we're talking SQL Server or MySQL. If nothing else, I'll start a process as the service account SQL Server is running under and connect as that account (since that account has to have access and has to be a member of the sysadmin fixed server role. Like I said, if an administrator wants in, you can't stop him or her.
With that said, let's talk about how to make it more difficult. First and foremost, you can restrict the permissions on the executable for SSMS Express. This is NTFS file system level. SQL Server (and SQL Server Express) doesn't make use of this. Therefore, you can change the permissions to what you want.
If the SQL Server isn't being seen on the network, then tighten down its network configuration accordingly. Only Shared Memory should be needed. This would mean you have to get access to the PC to access SQL Server.
Finally, restrict BUILTIN\Administrators, if it's still there in the SQL Server Express instance. Make sure the service account has been manually added and has the correct permissions.
K. Brian Kelley
@kbriankelley
October 26, 2009 at 12:56 pm
thanks a lot for you help, I'll try these workaround at my client's site.
I really appreciate your time,
regards,
Dominic Gagné
Montréal, Canada
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply