One database, multiple SQL Server instances. Permissions issue with NTFS
For specific security reasons we use standalone laptops with:
- Windows 7 Enterprise
- No connected networks
- SQL Server 2008 R2
These laptops shares a USB drive with BitLocker encryption with the database files (.mdf and .ldf) they need to use.
Recently we replaced the USB drive for a larger one and also replaced the file system from FAT32 to NTFS. This will allow the database to grow over the FAT32 limitations.
The issue is now that it's NTFS it has file level permissions and SQL Server now sets his own permissions on the database files when it attach and detach the database files. Since the OS users are not shared between the laptops, the laptop that has set the file permissions on the database is now the unique laptop able to attach the files. The other laptops does not recognize the SQL Server security group generated for the SQL Server users access SQLServerMSSQLUser${Computer Name}${SQL Instance}. Windows also does not let me add this group to the "Administrators" group.
To manage the access on the database we had to use SQL Server users instead of computer accounts. The sa user is the one attaching/detaching the database through a software the users run to query the database.
Since SQL Server always keep the group "Administrators" in the DB files permissions we can manually log as a computer administrator and change the permissions but we need some way that will be transparent to the users.
Our plan B would be to virtualize the environment with SQL Server and the database files and keep the VM image on the NTFS Bitlocker drive but we keep this solution as a last resort.
I would have liked to apply a kind of mask of permissions on the database files so that when MS SQL changes the permissions it includes groups like "Authenticated users" but sadly I haven't found a way and SQL Server removes any groups I add to the NTFS security permissions on the DB files.
Any solutions could work around or something I missed could allow to share the DB files between the laptops on the USB drive?
Thanks
sql-server ntfs edited Nov 15 '18 at 16:43
marc_s
581k13011211268
asked Nov 15 '18 at 16:14
Marc-Antoine JutrasMarc-Antoine Jutras
3116
add a comment |
For specific security reasons we use standalone laptops with:
- Windows 7 Enterprise
- No connected networks
- SQL Server 2008 R2
These laptops shares a USB drive with BitLocker encryption with the database files (.mdf and .ldf) they need to use.
Recently we replaced the USB drive for a larger one and also replaced the file system from FAT32 to NTFS. This will allow the database to grow over the FAT32 limitations.
The issue is now that it's NTFS it has file level permissions and SQL Server now sets his own permissions on the database files when it attach and detach the database files. Since the OS users are not shared between the laptops, the laptop that has set the file permissions on the database is now the unique laptop able to attach the files. The other laptops does not recognize the SQL Server security group generated for the SQL Server users access SQLServerMSSQLUser${Computer Name}${SQL Instance}. Windows also does not let me add this group to the "Administrators" group.
To manage the access on the database we had to use SQL Server users instead of computer accounts. The sa user is the one attaching/detaching the database through a software the users run to query the database.
Since SQL Server always keep the group "Administrators" in the DB files permissions we can manually log as a computer administrator and change the permissions but we need some way that will be transparent to the users.
Our plan B would be to virtualize the environment with SQL Server and the database files and keep the VM image on the NTFS Bitlocker drive but we keep this solution as a last resort.
I would have liked to apply a kind of mask of permissions on the database files so that when MS SQL changes the permissions it includes groups like "Authenticated users" but sadly I haven't found a way and SQL Server removes any groups I add to the NTFS security permissions on the DB files.
Any solutions could work around or something I missed could allow to share the DB files between the laptops on the USB drive?
Thanks
sql-server ntfs edited Nov 15 '18 at 16:43
marc_s
581k13011211268
asked Nov 15 '18 at 16:14
Marc-Antoine JutrasMarc-Antoine Jutras
3116
1
Later versions of SQL Server don't use local groups for file security, and SQL Server 2008 R2 is approaching end-of-support. So this may be a problem that goes away when you upgrade. In modern SQL Server the file ACL is created for the per-service SID, eg NT ServiceMSSQLSERVER, which has a single well-known SID.
– David Browne - Microsoft
Nov 15 '18 at 17:01
@DavidBrowne-Microsoft I did not know about this particularity of the single SID. I think this could be a good solution for us! Thanks
– Marc-Antoine Jutras
Nov 15 '18 at 17:59
add a comment |
For specific security reasons we use standalone laptops with:
- Windows 7 Enterprise
- No connected networks
- SQL Server 2008 R2
These laptops shares a USB drive with BitLocker encryption with the database files (.mdf and .ldf) they need to use.
Recently we replaced the USB drive for a larger one and also replaced the file system from FAT32 to NTFS. This will allow the database to grow over the FAT32 limitations.
The issue is now that it's NTFS it has file level permissions and SQL Server now sets his own permissions on the database files when it attach and detach the database files. Since the OS users are not shared between the laptops, the laptop that has set the file permissions on the database is now the unique laptop able to attach the files. The other laptops does not recognize the SQL Server security group generated for the SQL Server users access SQLServerMSSQLUser${Computer Name}${SQL Instance}. Windows also does not let me add this group to the "Administrators" group.
To manage the access on the database we had to use SQL Server users instead of computer accounts. The sa user is the one attaching/detaching the database through a software the users run to query the database.
Since SQL Server always keep the group "Administrators" in the DB files permissions we can manually log as a computer administrator and change the permissions but we need some way that will be transparent to the users.
Our plan B would be to virtualize the environment with SQL Server and the database files and keep the VM image on the NTFS Bitlocker drive but we keep this solution as a last resort.
I would have liked to apply a kind of mask of permissions on the database files so that when MS SQL changes the permissions it includes groups like "Authenticated users" but sadly I haven't found a way and SQL Server removes any groups I add to the NTFS security permissions on the DB files.
Any solutions could work around or something I missed could allow to share the DB files between the laptops on the USB drive?
Thanks
sql-server ntfs edited Nov 15 '18 at 16:43
marc_s
581k13011211268
asked Nov 15 '18 at 16:14
Marc-Antoine JutrasMarc-Antoine Jutras
3116
For specific security reasons we use standalone laptops with:
- Windows 7 Enterprise
- No connected networks
- SQL Server 2008 R2
These laptops shares a USB drive with BitLocker encryption with the database files (.mdf and .ldf) they need to use.
Recently we replaced the USB drive for a larger one and also replaced the file system from FAT32 to NTFS. This will allow the database to grow over the FAT32 limitations.
The issue is now that it's NTFS it has file level permissions and SQL Server now sets his own permissions on the database files when it attach and detach the database files. Since the OS users are not shared between the laptops, the laptop that has set the file permissions on the database is now the unique laptop able to attach the files. The other laptops does not recognize the SQL Server security group generated for the SQL Server users access SQLServerMSSQLUser${Computer Name}${SQL Instance}. Windows also does not let me add this group to the "Administrators" group.
To manage the access on the database we had to use SQL Server users instead of computer accounts. The sa user is the one attaching/detaching the database through a software the users run to query the database.
Since SQL Server always keep the group "Administrators" in the DB files permissions we can manually log as a computer administrator and change the permissions but we need some way that will be transparent to the users.
Our plan B would be to virtualize the environment with SQL Server and the database files and keep the VM image on the NTFS Bitlocker drive but we keep this solution as a last resort.
I would have liked to apply a kind of mask of permissions on the database files so that when MS SQL changes the permissions it includes groups like "Authenticated users" but sadly I haven't found a way and SQL Server removes any groups I add to the NTFS security permissions on the DB files.
Any solutions could work around or something I missed could allow to share the DB files between the laptops on the USB drive?
Thanks
sql-server ntfs sql-server ntfs edited Nov 15 '18 at 16:43
marc_s
581k13011211268
asked Nov 15 '18 at 16:14
Marc-Antoine JutrasMarc-Antoine Jutras
3116
edited Nov 15 '18 at 16:43
marc_s
581k13011211268
asked Nov 15 '18 at 16:14
Marc-Antoine JutrasMarc-Antoine Jutras
3116
edited Nov 15 '18 at 16:43
marc_s
581k13011211268
edited Nov 15 '18 at 16:43
marc_s
581k13011211268
edited Nov 15 '18 at 16:43
marc_s
581k13011211268
581k13011211268
asked Nov 15 '18 at 16:14
Marc-Antoine JutrasMarc-Antoine Jutras
3116
asked Nov 15 '18 at 16:14
Marc-Antoine JutrasMarc-Antoine Jutras
3116
asked Nov 15 '18 at 16:14
Marc-Antoine JutrasMarc-Antoine Jutras
3116
3116
1
Later versions of SQL Server don't use local groups for file security, and SQL Server 2008 R2 is approaching end-of-support. So this may be a problem that goes away when you upgrade. In modern SQL Server the file ACL is created for the per-service SID, eg NT ServiceMSSQLSERVER, which has a single well-known SID.
– David Browne - Microsoft
Nov 15 '18 at 17:01
@DavidBrowne-Microsoft I did not know about this particularity of the single SID. I think this could be a good solution for us! Thanks
– Marc-Antoine Jutras
Nov 15 '18 at 17:59
add a comment |
1
Later versions of SQL Server don't use local groups for file security, and SQL Server 2008 R2 is approaching end-of-support. So this may be a problem that goes away when you upgrade. In modern SQL Server the file ACL is created for the per-service SID, eg NT ServiceMSSQLSERVER, which has a single well-known SID.
– David Browne - Microsoft
Nov 15 '18 at 17:01
@DavidBrowne-Microsoft I did not know about this particularity of the single SID. I think this could be a good solution for us! Thanks
– Marc-Antoine Jutras
Nov 15 '18 at 17:59
1
1
Later versions of SQL Server don't use local groups for file security, and SQL Server 2008 R2 is approaching end-of-support. So this may be a problem that goes away when you upgrade. In modern SQL Server the file ACL is created for the per-service SID, eg NT ServiceMSSQLSERVER, which has a single well-known SID.
– David Browne - Microsoft
Nov 15 '18 at 17:01
Later versions of SQL Server don't use local groups for file security, and SQL Server 2008 R2 is approaching end-of-support. So this may be a problem that goes away when you upgrade. In modern SQL Server the file ACL is created for the per-service SID, eg NT ServiceMSSQLSERVER, which has a single well-known SID.
– David Browne - Microsoft
Nov 15 '18 at 17:01
@DavidBrowne-Microsoft I did not know about this particularity of the single SID. I think this could be a good solution for us! Thanks
– Marc-Antoine Jutras
Nov 15 '18 at 17:59
@DavidBrowne-Microsoft I did not know about this particularity of the single SID. I think this could be a good solution for us! Thanks
– Marc-Antoine Jutras
Nov 15 '18 at 17:59
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53323608%2fone-database-multiple-sql-server-instances-permissions-issue-with-ntfs%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53323608%2fone-database-multiple-sql-server-instances-permissions-issue-with-ntfs%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Later versions of SQL Server don't use local groups for file security, and SQL Server 2008 R2 is approaching end-of-support. So this may be a problem that goes away when you upgrade. In modern SQL Server the file ACL is created for the per-service SID, eg NT ServiceMSSQLSERVER, which has a single well-known SID.
– David Browne - Microsoft
Nov 15 '18 at 17:01
@DavidBrowne-Microsoft I did not know about this particularity of the single SID. I think this could be a good solution for us! Thanks
– Marc-Antoine Jutras
Nov 15 '18 at 17:59