InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 15:59:55 GMT20http://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspx[quote][b]Sean Lange (3/20/2013)[/b][hr]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.[/quote]Your right and LightSwitch apps can be deployed to Azure and run in IE. In fact, if I can figure out how to programmatically automate filling out an official form on the server and then load that form as a pdf in the browser so it can be printed out and signed in ink as the law requires I would not be messing around on the desktop at all.Wed, 20 Mar 2013 21:05:28 GMTclintonGhttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspx[quote][b]GilaMonster (3/20/2013)[/b][hr][quote][b]clintonG (3/20/2013)[/b][hr]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.[/quote]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.[/quote]Yes, Yes, Yes lolI understand how to correctly backup a SQL Server and I'm only intending to locate the mdf/ldf in the file system because it gives me one last chance to at least try to get the LightSwitch app functional if and when one of the employees blows this sh!t up.Do you have any experience with PowerShell? A thought just came to mind that perhaps I could build backing up the database correctly using script but generally I flake out and use SQL Management Studio so that's more to learn to do.Wed, 20 Mar 2013 21:02:23 GMTclintonGhttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspx[quote][b]Lynn Pettis (3/20/2013)[/b][hr]Is this some sort of inventory database?[/quote]In a manner of speaking yes, this is a type of inventory database of all transactions when a pawnbroker or second-hand sales shop purchases an item from somebody that walks in off the street trying to sell your new Samung 55" Smart TV they obtained from a burglary. So the state made a law that all these types of transactions must conform to a set of lawful mandates, a good part of which is describing the items with a level of detail that may help identify it if and when police put two and two together. I would check your state for the same newly emerging laws. My biggest problem right now is how to programmatically automate filling out an "official" form implemented as a Microsoft Word Form or a filllable pdf form.Wed, 20 Mar 2013 20:48:13 GMTclintonGhttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspxSounds 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.Wed, 20 Mar 2013 12:56:10 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspx[quote][b]clintonG (3/20/2013)[/b][hr][quote]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[/quote] Not going to be possible as the machines are shared and serve multiple uses. If I could encourage a separate machine I would.[/quote]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.Wed, 20 Mar 2013 12:14:03 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspx[quote][b]clintonG (3/20/2013)[/b][hr]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.[/quote]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.Wed, 20 Mar 2013 12:11:41 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspxIs this some sort of inventory database?Wed, 20 Mar 2013 09:59:13 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspxi hear your point but....A. They only have themselves to blame thenB. They only have themselves to blame thenC. see point AYou aren't doing yourself any favors and this one will come back to bite you in the @ssWed, 20 Mar 2013 09:22:48 GMTkevaburghttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspx[quote] 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! :)[/quote]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 itB.) Anything that gets done is going to be f*cked up anyway by some clown employeeC.) Item A starts all over againWed, 20 Mar 2013 09:17:56 GMTclintonGhttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspx[quote]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[/quote] Not going to be possible as the machines are shared and serve multiple uses. If I could encourage a separate machine I would.[quote]You're not doing some 'copy data files and attach to a local instance' trick are you?[/quote]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.Wed, 20 Mar 2013 08:53:43 GMTclintonGhttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspxNow 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! :)Wed, 20 Mar 2013 02:25:58 GMTkevaburghttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspx[quote][b]clintonG (3/19/2013)[/b][hr]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. [/quote]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[quote]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.[/quote]??? 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 ServerYou're not doing some 'copy data files and attach to a local instance' trick are you?Wed, 20 Mar 2013 01:41:15 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspx[quote][b]GilaMonster (3/19/2013)[/b][hr][quote][b]clintonG (3/19/2013)[/b][hr][quote][b]kevaburg (3/18/2013)[/b][hr]lol![/quote]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?[/quote]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.[/quote]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.Tue, 19 Mar 2013 17:54:53 GMTclintonGhttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspxFirstly, 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!Tue, 19 Mar 2013 09:05:33 GMTkevaburghttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspx[quote][b]kevaburg (3/18/2013)[/b][hr]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?[/quote]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 ROLEIt meets my objective to disallow a logged in user permission to delete any data while using a LightSwitch 2-tier app.Tue, 19 Mar 2013 08:51:51 GMTclintonGhttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspx[quote][b]clintonG (3/19/2013)[/b][hr][quote][b]kevaburg (3/18/2013)[/b][hr]lol![/quote]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?[/quote]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.Tue, 19 Mar 2013 08:51:31 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspx[quote][b]kevaburg (3/18/2013)[/b][hr]lol![/quote]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.Tue, 19 Mar 2013 08:43:50 GMTclintonGhttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspxlol!Mon, 18 Mar 2013 12:02:31 GMTkevaburghttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspx[quote][b]clintonG (3/18/2013)[/b][hr]However doesn't Group Policy Editor remain accessible to any Windows 7 Administrator anyway?[/quote]Err, huh?Mon, 18 Mar 2013 09:32:30 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspxI have just created a role called "DENY DELETE", and assigned a user called "TESTUSER" to it after adding that user to new role. Is that sufficient?Mon, 18 Mar 2013 08:48:57 GMTkevaburghttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspxWhen 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?Mon, 18 Mar 2013 08:43:10 GMTkevaburghttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspx[quote][b]GilaMonster (3/17/2013)[/b][hr][quote][b]clintonG (3/17/2013)[/b][hr] The SQLExpress database will be intsalled on a share of a Windows7 machine[/quote]Errrr....SQL is not Access. It's not a file-based database that sits on a share. It's a service that other machines connect to.With the SQL Service running on a Windows 7 machine, anyone who has administrative access to that Windows 7 machine can do what they like to your database (and that includes dropping it, uninstalling the service, etc).You need to lock that machine down and treat it like a server if it's serving data.[/quote]I understand thank you. However doesn't Group Policy Editor remain accessible to any Windows 7 Administrator anyway?So with your insights and those of others I've worked through creating a login, user, flexible role with DENY DELETE and the app does not allow deleting data when it is being used which is all I wanted to learn get done at the moment.Mon, 18 Mar 2013 08:22:47 GMTclintonGhttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspx[quote][b]clintonG (3/17/2013)[/b][hr] The SQLExpress database will be intsalled on a share of a Windows7 machine[/quote]Errrr....SQL is not Access. It's not a file-based database that sits on a share. It's a service that other machines connect to.With the SQL Service running on a Windows 7 machine, anyone who has administrative access to that Windows 7 machine can do what they like to your database (and that includes dropping it, uninstalling the service, etc).You need to lock that machine down and treat it like a server if it's serving data.Sun, 17 Mar 2013 15:06:39 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspx[quote......TL;DR If you give the database to someone (put it on their machine), they can do what they like.[/quote]This and similar projects use P2P LANs. The SQLExpress database will be intsalled on a share of a Windows7 machine and the LightSwitch client installed on other Windows7 machines will use a connection string to use the database.That's the architecture I have to work with. So where things are at for me right now I am looking at creating a Role that does not allow delete and I will add users to that role. If that's the best I can do that will have to do.Sun, 17 Mar 2013 14:39:37 GMTclintonGhttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspx[quote][b]clintonG (3/17/2013)[/b][hr]However all I really need to do is disallow Users from deleting data while using a LightSwitch app and I will continue to read and learn how to do so[/quote]Let me simplify...If the database is on the user's machine and the user has local administrator permissions, there is nothing you can do to keep that user out of the database if they want access. You can slow them down, that's all.If you want to restrict access, the database needs to be on a server. The bear minimum of people must be admin of that server (typically sysadmins and maybe DBAs), the users then have logins to SQL and only the rights you want to give them.TL;DR If you give the database to someone (put it on their machine), they can do what they like.Sun, 17 Mar 2013 12:25:31 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspx[quote][b]clintonG (3/17/2013)[/b][hr][quote][b]kevaburg (3/17/2013)[/b][hr]Logins created within the instance have by default no automatic access to any database within that instance. Each created user needs to be explicitly mapped to a database and assigned roles as appropriate. The master database contains the structures of the databases on the instance and access to it does not imply access to other databases. It is really worth reading the security articles pertaining to fixed server roles and database level permissions to ensure to don't end up opening a security hole where you don't want one.[/quote]Thanks for comments.I've been reading more about security and fixed server roles.This article explains how my endeavor may be futile anyway...http://www.netspi.com/blog/2012/08/16/sql-server-2008-local-administrator-privilege-escalation/However all I really need to do is disallow Users from deleting data while using a LightSwitch app and I will continue to read and learn how to do so[/quote]Let's make it easier for others to read:[b][url]http://www.netspi.com/blog/2012/08/16/sql-server-2008-local-administrator-privilege-escalation/[/url][/b]Sun, 17 Mar 2013 12:12:27 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspx[quote][b]kevaburg (3/17/2013)[/b][hr]Logins created within the instance have by default no automatic access to any database within that instance. Each created user needs to be explicitly mapped to a database and assigned roles as appropriate. The master database contains the structures of the databases on the instance and access to it does not imply access to other databases. It is really worth reading the security articles pertaining to fixed server roles and database level permissions to ensure to don't end up opening a security hole where you don't want one.[/quote]Thanks for comments.I've been reading more about security and fixed server roles.This article explains how my endeavor may be futile anyway...http://www.netspi.com/blog/2012/08/16/sql-server-2008-local-administrator-privilege-escalation/However all I really need to do is disallow Users from deleting data while using a LightSwitch app and I will continue to read and learn how to do soSun, 17 Mar 2013 08:45:12 GMTclintonGhttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspxLogins created within the instance have by default no automatic access to any database within that instance. Each created user needs to be explicitly mapped to a database and assigned roles as appropriate. The master database contains the structures of the databases on the instance and access to it does not imply access to other databases. It is really worth reading the security articles pertaining to fixed server roles and database level permissions to ensure to don't end up opening a security hole where you don't want one.Sun, 17 Mar 2013 07:05:10 GMTkevaburghttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspxI've been reading BOL but the nuance you provided is not there so thanks for some added clarification.Fri, 15 Mar 2013 14:46:44 GMTclintonGhttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspx[quote][b]clintonG (3/14/2013)[/b][hr]When I create a user and select master as the default database does that mean or imply that user can login to other databases on the same instance of SQLExpress 2012? If so how is a user created that can only login to a specific database? I understand a User can be mapped to a specific database but in certain circumstances the User must be created manually --before the database-- because the database the User is logging into is being generated by a sql script generated by a tool; Visual Studio LightSwitch 2012 to be specific.Lots of generation going on except in my brain. lol[/quote]Only a login can have the "default_database" setting, a user cannot. Default database setting let you choose a database which this login will be connected to whenever he logins into SQL Server (If the user has not specified any database name explicitly). Logins & Users are completely different from each other. Logins are create at server scope whereas users are created in database scope.So, You can create users only after the database is created.Another important thing to note here is, though SQL Server allows you to set [master] database as the default_database for new logins that you are creating, it doesn't mean they will be granted access to master database as a user. [master] database will just work as a landing database for them, nothing else.For more information on this you can check below link:[url=http://www.sujeetsingh.com/2012/12/logins-users-in-sql-server.html][b]Logins & Users in SQL Server[/b][/url]Fri, 15 Mar 2013 06:55:53 GMTDivine Flamehttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspx[quote][b]clintonG (3/14/2013)[/b][hr]I understand a User can be mapped to a specific database but in certain circumstances the User must be created manually --before the database-- because the database the User is logging into is being generated by a sql script generated by a tool; Visual Studio LightSwitch 2012 to be specific.[/quote]Unless I'm reading this incorrectly, how would LightSwitch create a user before the database actually exists unless, maybe, you are referring to Logins?Fri, 15 Mar 2013 05:34:54 GMTOTFhttp://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspxIf you create a login and set 'master' as default database then, the user will only be created in the master database. The login/user will not have access to any of the user/system databases untill and unless you map the user to other databases. Upon trying to access any other database it should result into the belwo error:TITLE: Microsoft SQL Server Management Studio------------------------------The database 'MyUserDB' is not accessible. (ObjectExplorer)------------------------------Fri, 15 Mar 2013 00:45:31 GMTarunyadav007http://www.sqlservercentral.com/Forums/Topic1431330-1292-1.aspxWhen I create a user and select master as the default database does that mean or imply that user can login to other databases on the same instance of SQLExpress 2012? If so how is a user created that can only login to a specific database? I understand a User can be mapped to a specific database but in certain circumstances the User must be created manually --before the database-- because the database the User is logging into is being generated by a sql script generated by a tool; Visual Studio LightSwitch 2012 to be specific.Lots of generation going on except in my brain. lolThu, 14 Mar 2013 19:58:50 GMTclintonG