Default NTFS permissions on new DB
-
We have loaded the hotfix that takes care of NTFS permissions being reset when detaching and attaching a DB - but when a user creates a New DB - the NTFS file permissions get reset and are not the permissions that we want to be set as default. The permissions we want are those assigned to the folder which the new DB is created in - I would like the permissions to be inherited if possible - not reset to what 2005 "thinks" they should be.
What am I missing here?
Thanks!
-
SQL Server is RDBMS(relational database management systems) so a SQL Server database is MDF(Microsoft data file), LDF(Log data file), Secondary data file and .bak all of the above are stored in the Microsoft SQL Server folder in programs, the former are in the data sub folder while the later is in the Backup subfolder. I don't think NTFS permissions are relevant because the third part of the relational model is DCL(data control language) which is implemented by Microsoft as object permissions so unless the database is using file system Encryption you have no control over what SQL Server 2005 does with your database and .bak files. However you can change the location during creation but remember this is not Access you cannot do what you like with all of the files I have explained because the server is the runtime not the database. Hope this helps.
Kind regards,
Gift Peddie -
SQL Server is going to automatically lock down permissions as tight as possible (the whole secure by default mentality) for the data files, which is why you're seeing the behavior you're seeing. It's also making sure it has the proper access to the data files for the SQL Server service account. About the only way I can think of around this is to script database creation from the command line and the set the permissions on the new files using cacls, xcacls, or subinacl. I know that's a roundabout approach to getting the job done, but it's the only automated method that comes to mind.
K. Brian Kelley
@kbriankelley -
The permissions are relevant when you want your people to be able to move DB's from a dev to test or prod environment. The default security means that the data analyst needs to either be a member of the local admin group (way too much access)- or manually added to the NTFS permissions. The issue with manually adding is that a DA might create new DB's all the time, and then not be able to move them about as needed. Tracking the necessary DACLS becomes a nightmare in a large environment.
In 2K we would have a folder structure like D:\SQLData where that folder had the permissions necessary for anyone working with the data to copy, move, etc. Now that folder security structure is ignored and SQL 90 replaces all permissions when a new DB is created within that folder structure.
MS has already release a hotfix because a detach/re-attach sequence would reset the NTFS permissions on a DB. Now they just need to do the same for a new DB (effectively the same issue).
-
Tracking the necessary DACLS becomes a nightmare in a large environment.
Aren't the DAs in a security group already? Couldn't you just script the addition of the ACE for that security group?
K. Brian Kelley
@kbriankelley -
OK let me put it this way these file permissions are not relevant to skilled System Admins because the Backup and Restore wizard moves databases created in where ever, whatever SQL Server in the World and will recreate that database anywhere with a SQL Server is running.
Kind regards,
Gift Peddie -
I'm going to have to go with the original poster here... I'd rather do a file copy than use the wizard. There are a whole host of reasons for this, one of which is the capability to script the functionality in a parameterized fashion so I can automate this kind of thing...
K. Brian Kelley
@kbriankelley -
If he is running testing and dev databases Backup and Restore moves everything like the original database in a web application that is important because you are moving all the permissions as the original.
Kind regards,
Gift Peddie -
No. He's referring to file system permissions, not permissions inside SQL Server. In that case he needs for certain personnel to be able to move the databases without being full blown administrators on the dev systems. Because SQL Server 2005 intentionally breaks inheritance with respect to NTFS permissions, the individual files do not get the same permissions as the folder. So even though he has set the permissions he desires at the folder level, when SQL Server creates new database and log files, it sets the permissions it thinks are needed... which doesn't happen to match what he needs. Remember, this is all at the file system level, not within SQL Server.
K. Brian Kelley
@kbriankelley -
Exactly. Its one of those things that changed from 2K to 2005 and its not the end of the world - but very inconvenient when you have years of business process built around the ability to move within the file system. Consider that we have hundreds and hundreds of DB's, thousands of security groups and over 200TB of storage in use - so this would be a nice problem to solve.
Thanks for the help guys - I'll post back if MS has anything to offer.
-
I did a quick search and didn't see it, so in case you haven't done so, have you submitted it as a suggestion here:
http://connect.microsoft.com/SQLServer
K. Brian Kelley
@kbriankelley -
I understand what both of you are saying and I still don't agree because his devs only need DDL creator role and Trace ON to create their dev databases in UNC. All others should be sent by email to who needs it and number of terabytes is not the issue because Network Admins is Network Admin and in correct implementation SQL Server should be a member server not part of AD NTFS volumes. The reality is SQL Server owns the folder so it controls what happens in the folder I think that is correct.
Kind regards,
Gift Peddie -
Okay, let's clear up a few things.
1) It's not an unusual practice to create a development database and then copy or move it to another environment. What I've done in the past is detach the database, copy the data files, and then attached at the new location. This is the sort of thing he's trying to do and it tends to be a lot faster than using wizards to script out all the database objects, all the data, etc., and apply them to a different location. A LOT faster. The same is true of this method versus backup/restore. The detach, copy, attach tends to be faster than that, too. In fact, Andy Warren has an article about moving databases in this fashion because it is so much faster.
2) NTFS - New Technology File System. It is one of two file systems Windows 2000/XP/2003 natively support for hard drives (the other is FAT and its variants). By default, all drives should be NTFS because of the security and integrity features. This has nothing to do with an Active Directory domain controller, which is what I think you mean. NTFS is what allows permissions at the file system level. Operating systems like FAT and FAT32 do not.
3) SQL Server does not control the folder any more than IIS controls C:\Inetpub. SQL Server isn't altering the permissions on the folder. In fact, it is ignoring them. SQL Server is breaking inheritance from the folder to the files and setting its own permissions on the files. And that's the issue.
Maybe the following links will clarify things:
http://www.windowsecurity.com/articles/Understanding-Windows-NTFS-Permissions.html
http://www.windowsitlibrary.com/Content/592/toc.html
http://www.microsoft.com/mspress/books/sampchap/6103.aspx
K. Brian Kelley
@kbriankelley -
SQL Server is not ignoring anything the MDF and LDF belong to the data sub folder in SQL Server folder in programs so how is SQL Server breaking inheretance of file permissions. I am glad I decided to add MCSE to the MCDBA so security people cannot come and explain their desire as valid permissions dependency because it is not.
Kind regards,
Gift Peddie -
Gift Peddie,
On one of your SQL Server 2005 servers, navigate to where the data files are stored at the file system level. This is typically \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data. Right click on one of the data files and choose Properties. Then click on the Security tab. Then click on the Advanced button. You should see a dialog box similar to the one below my message. I've highlighted the setting which tells whether or not inheritance is on by marking it with a big, red rectangle. Notice that this setting is NOT checked. That's because SQL Server 2005 intentionally breaks inheritance for the data files.
And yes, I'm a security person, but this has nothing to do with my desire. This has to do with how it actually works. And typically you don't break inheritance unless you absolutely must. It can make administration and maintenance a nightmare. When you have hundreds of thousands of files and directories comprising terabytes of data, this problem can result in many, many man-hours of extra effort to maintain those files (speaking from personal experience). But also when you have a handful of files and only a couple of directories, this can come back to haunt you, as is the case here. SQL Server 2005 doesn't have a real need to break inheritance, but it does anyway. And that's a deviation from a generally accepted practice.
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply