Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Do Users based on logins in master have access to other databases? Expand / Collapse
Author
Message
Posted Tuesday, March 19, 2013 5:54 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 9:02 PM
Points: 33, Visits: 37
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.




Post #1432948
Posted Wednesday, March 20, 2013 1:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 42,765, Visits: 35,863
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1433030
Posted Wednesday, March 20, 2013 2:25 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 5:18 AM
Points: 311, Visits: 486
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! :)
Post #1433056
Posted Wednesday, March 20, 2013 8:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 9:02 PM
Points: 33, Visits: 37

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.



Post #1433276
Posted Wednesday, March 20, 2013 9:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 9:02 PM
Points: 33, Visits: 37

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



Post #1433300
Posted Wednesday, March 20, 2013 9:22 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 5:18 AM
Points: 311, Visits: 486
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
Post #1433305
Posted Wednesday, March 20, 2013 9:59 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:54 PM
Points: 23,227, Visits: 31,921
Is this some sort of inventory database?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1433326
Posted Wednesday, March 20, 2013 12:11 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 42,765, Visits: 35,863
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1433396
Posted Wednesday, March 20, 2013 12:14 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 42,765, Visits: 35,863
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1433397
Posted Wednesday, March 20, 2013 12:56 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:23 PM
Points: 13,272, Visits: 12,103
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1433429
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse