March 18, 2013 at 12:02 pm
lol!
March 19, 2013 at 8:43 am
kevaburg (3/18/2013)
lol!
I don't know what's supposed to be so funny. How else could somebody lock down a Windows 7 client OS functioning as a "server" than to use Group Policy Editor?
Furthermore, if you ever watch Law and Order its always the guy with the Windows Administrator account that runs the Group Polcy Editor that does all the whack job anyway and as far as I've been able to determine the only thing that is really laughable is a secured implementation of a Windows client OS.
My point is I know there are lots of ways to lock sh!t down but its not worth my time or trouble to try to learn or even spend the time to do so if I am not going to get paid for it and what I do can be subverted anyway which is why I always write a "good faith" clause in all work agreements.
Furthermore, as I said, the only way I know of doing so on a client machine is by using Group Policy Editor and as I've indicated there is no way to lock down Group Policy Editor that cannot be hacked by the Administrator account anyway.
March 19, 2013 at 8:51 am
clintonG (3/19/2013)
kevaburg (3/18/2013)
lol!I don't know what's supposed to be so funny. How else could somebody lock down a Windows 7 client OS functioning as a "server" than to use Group Policy Editor?
Start by making sure that 3/4 of the staff don't have the administrator password. Strong administrator password, limited user access with minimal permissions. Not talking about group policy, talking about restricting logins in the first place. Unless someone has a need to administer the particular machine, they should have no rights whatsoever to the machine.
Once you've limited the access to just the few people who need to administer the machine, then you can do a proper lock down and harden if necessary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 19, 2013 at 8:51 am
kevaburg (3/18/2013)
When I look at the SSMS console for a database, I can see under the Permissions section that for a given user/group, DENY DELETE can be granted. Is that not what you are looking for?
I've got it done this way...
--// CREATE THE LOGIN
--// CREATE THE FLEXIBLE ROLE
--// GRANT, DENY, REVOKE PERMISSIONS FOR THE ROLE
--// ADD MEMBERS TO THE ROLE
It meets my objective to disallow a logged in user permission to delete any data while using a LightSwitch 2-tier app.
March 19, 2013 at 9:05 am
Firstly, I wasn't laughing at you, I was laughing at Gails response!
Secondly, simply having Group Policy Editor is not the opportunity that people look for to hack into a system of any kind. It is the permission to edit the Group Policy itself that causes the problems and if you have Domains Admins that are likely to abuse that right then you problem is bigger than you think.
If you think Windows 7 security is laughable then perhaps a better understanding of Active Directory and Group Policy Management is necessary. Laughable for me is someone has decided to place a production (I assume) database on a laptop and the admins did nothing to stop it! It is a database service and deserves more respect than that.
Above all, use the concept of minimal privilege. It is true that if someone really wants to get in, they will. But that is not the get out clause for admins that distribute admin passwords to all and sundry and not the excuse to use for not understanding the (admittedly) complex security mechanisms that will protect your organisation.
The bottom line: Get a new box for the database, even if it is only a good PC!
March 19, 2013 at 5:54 pm
GilaMonster (3/19/2013)
clintonG (3/19/2013)
kevaburg (3/18/2013)
lol!I don't know what's supposed to be so funny. How else could somebody lock down a Windows 7 client OS functioning as a "server" than to use Group Policy Editor?
Start by making sure that 3/4 of the staff don't have the administrator password. Strong administrator password, limited user access with minimal permissions. Not talking about group policy, talking about restricting logins in the first place. Unless someone has a need to administer the particular machine, they should have no rights whatsoever to the machine.
Once you've limited the access to just the few people who need to administer the machine, then you can do a proper lock down and harden if necessary.
I'm deleting Guest and I wasn't planning on giving anybody the sa. I'll also change the sa password to a GUID. I'm installing SQLExpress and if they want an sa they can install another instance themselves or hire me to build other instances.
I am concerned with a dilemma that requires the .mdf and logs in a share at My Documents > My Data folder so the ClickOnce LightSwitch apps (Silverlight clients) can be updated with a newer ClickOnce instance if needed. That is actually the same share I referred to in earlier comments and where I need to keep looking into Group Policy Editor.
What I should really do is learn how to remotely access a Windows 7 machine for any further hands-on if and when.
March 20, 2013 at 1:41 am
clintonG (3/19/2013)
I'm deleting Guest and I wasn't planning on giving anybody the sa. I'll also change the sa password to a GUID. I'm installing SQLExpress and if they want an sa they can install another instance themselves or hire me to build other instances.
Don't delete guest, that can cause problems. Just make sure it has no rights. Disable sa. Also, I wasn't talking about SQL permissions, you need to lock down the windows machine, limit administrative access, make sure that no one has permissions to the machine unless they need it
I am concerned with a dilemma that requires the .mdf and logs in a share at My Documents > My Data folder so the ClickOnce LightSwitch apps (Silverlight clients) can be updated with a newer ClickOnce instance if needed. That is actually the same share I referred to in earlier comments and where I need to keep looking into Group Policy Editor.
??? A SQL database can have its mdf and ldf anywhere that the SQL service has permission to, they don't have to be accessible to the outside world and to be honest they should be in a directory locked down so that only administrator and SQL have rights. External clients should never be able to access the database files directly, they access them purely via SQL Server
You're not doing some 'copy data files and attach to a local instance' trick are you?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 20, 2013 at 2:25 am
Now for a couple of tips that I employ on the network. Maybe they are relevant to you, maybe not but if they can help then all the better.
1.I don't delete the Guest account, rather leave it disabled. There have been short notice occasions whereby the Guest account has proved useful.
2.The sa account remains disabled at all times unless an authorised DBA requires it. It has as you have done, a complex password which is stored in a secure location. Unfortunately, stopping anyone from having the sa account may not be something that is allowed within company policy, but ensuring tight restrictions will certainly help.
I understand you concerns about having the .mdf and .ldf(?) files in a shared location! What is the reasoning behind that? Both files will be in constant use and cannot be modified at the file level. Is it planned to take the database offline and copy the files to another location on occasion? There are certainly some funny things going on with this particular customer I would say!
You mention quite often using the Group Policy Editor. Is this machine in a domain environment? If not then editing the Local Machine Policy wouild be far better although it will still be very restrictive in what it can do.
As for accessing a Windows 7 machine: RDP or Windows Remote Support utilities are the sorts of tools you need.
To be honest, I would strongly recommend disassociating yourself from this project because from what I have read up until now it can only end in tears.
A production database on a laptop.
Unlocked and widely available sa credentials.
User access to mdf and ldf datafiles.
Uncertain security settings to the host.
Disrespectful treatment of company data.
Uncertainty about SQL Server management.
This story is unlikely to have a happy end. Do your nerves (and your reputation) a favour and get the f*-/ out of Dodge! 🙂
March 20, 2013 at 8:53 am
Also, I wasn't talking about SQL permissions, you need to lock down the windows machine, limit administrative access, make sure that no one has permissions to the machine unless they need it
Not going to be possible as the machines are shared and serve multiple uses. If I could encourage a separate machine I would.
You're not doing some 'copy data files and attach to a local instance' trick are you?
Not to my foreknowledge but I may actually be trying to do a trick niavely as my intent to locate the mdf/ldf files at My Documents > My Data is to try to ensure the files get backed up. I was thinking if there was a problem they could easily be restored using an Attach.
March 20, 2013 at 9:17 am
To be honest, I would strongly recommend disassociating yourself from this project because from what I have read up until now it can only end in tears.
...
This story is unlikely to have a happy end. Do your nerves (and your reputation) a favour and get the f*-/ out of Dodge! 🙂
I certainly hear what's being advised.
Maybe you haven't noticed but a lot of people are selling their personal belongings to survive. Burglaries and thefts have skyrocketed. So the politicians passed laws that require all buyers such as pawnshops and resale shops to record and report all transactions to police. The shops have to report within 24hrs and they have to keep the item purchased for resale a specific period of time.
Its become a burden that requires many hours of time and shop owners are looking for an app to help them. I got interested because Visual Studio LightSwitch is rather ideal for this type of app and I've thought I could write an agreement that would cover my @ss for not having to spend the time and resources to lock down everything because
A.) They cannot and will not pay for it
B.) Anything that gets done is going to be f*cked up anyway by some clown employee
C.) Item A starts all over again
March 20, 2013 at 9:22 am
i hear your point but....
A. They only have themselves to blame then
B. They only have themselves to blame then
C. see point A
You aren't doing yourself any favors and this one will come back to bite you in the @ss
March 20, 2013 at 9:59 am
Is this some sort of inventory database?
March 20, 2013 at 12:11 pm
clintonG (3/20/2013)
Not to my foreknowledge but I may actually be trying to do a trick niavely as my intent to locate the mdf/ldf files at My Documents > My Data is to try to ensure the files get backed up. I was thinking if there was a problem they could easily be restored using an Attach.
No, no, no, no!!
Copying files is not a SQL Server backup. At best it gets you a DB that you can reattach, at worse it gets you a DB that refuses to reattach because it's inconsistent.
The way to take SQL backups is via BACKUP DATABASE ... TO DISK ... and have that resulting backup file taken off to other storage. Do Not make the common mistake of thinking that you can copy the file of an active, in-use SQL database and you have a working backup. That's Russian roulette with your database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 20, 2013 at 12:14 pm
clintonG (3/20/2013)
Also, I wasn't talking about SQL permissions, you need to lock down the windows machine, limit administrative access, make sure that no one has permissions to the machine unless they need it
Not going to be possible as the machines are shared and serve multiple uses. If I could encourage a separate machine I would.
In that case, all your securing attempts with SQL Server are a waste of time. If someone has administrative access to the machine, they can get full control of the SQL database to the point of deleting all the data or dropping the database entirely and there's not a damn thing you can do in SQL to stop them.
If you need to secure a database, the server it's on must be secured as well. If you haven't got the latter, you can't get the former either.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 20, 2013 at 12:56 pm
Sounds to me like this app you are working on would be an excellent candidate for SaaS (Software as a Service). In other words, you create the application and lease the privilege to use it to these pawn shops. That way you host the application server and the database server. None of the clients have access to any of the machines. Makes it super secure because the database is tucked away safely right next to the application code on your server.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply