SAM2009
asked on
How security is set on drives of Windows 2003 Server to let only specific SQL Server acces to them?
How security is set on drives of Windows Server 2003 to let only specific SQL Server acces to them?
For exemple:
- Windows 2003A, has SQL Server 2005 installed and has those drives: C, D, E,F
- Windows 2003B, has also SQL server 2005 installed and SQL Management Studio
Let's say:
1- I open SQL Management Studio on Windows 2003B and connect on SQL databases of Windows 2003A
2- When I try to restore a database from file, I can only acces to E and F drives of Windows 2003A
How security is set to get only acces to specific drives?
For exemple:
- Windows 2003A, has SQL Server 2005 installed and has those drives: C, D, E,F
- Windows 2003B, has also SQL server 2005 installed and SQL Management Studio
Let's say:
1- I open SQL Management Studio on Windows 2003B and connect on SQL databases of Windows 2003A
2- When I try to restore a database from file, I can only acces to E and F drives of Windows 2003A
How security is set to get only acces to specific drives?
Do you use a windows domain account or a local account?
On your SQL Servers, is it SQL Server authentication or Mixed mode authentication?
The thing is if you want to restore database file which are located on Windows 2003A,whateever the drive is, you must share the folder containing your restore files; and the user you are using to connect to Windows 2003A should have access to both Windows 2003A and Windows 2003B.
Any of your windows drive is protected by NTFS permissions until you share it ! Default permissions are set to let LOCAL MACHINE Users to acces the drives, meaning that if you are on Windows 2003A, all users(who are administrators of course) can access all drives. But by changing any of those NTFS permissions, you can add/remove a users, and therefore all/deny him the access to a drive.
Then when you share a drive, you set SHARE PERMISSIONS which are different from NTFS Permissions. Share Permissions allow you to tell WHO on your NETWORK can See, Read or Write you shared folder. NTFS permissions defines who can ACCESS your shared folder.
e.g: If you set permissions of User A to Write in your shared folder, and then on the NTFS permissions you deny him, he will see the folder but won't be able to Write into it !
Hope this answered your question.
On your SQL Servers, is it SQL Server authentication or Mixed mode authentication?
The thing is if you want to restore database file which are located on Windows 2003A,whateever the drive is, you must share the folder containing your restore files; and the user you are using to connect to Windows 2003A should have access to both Windows 2003A and Windows 2003B.
Any of your windows drive is protected by NTFS permissions until you share it ! Default permissions are set to let LOCAL MACHINE Users to acces the drives, meaning that if you are on Windows 2003A, all users(who are administrators of course) can access all drives. But by changing any of those NTFS permissions, you can add/remove a users, and therefore all/deny him the access to a drive.
Then when you share a drive, you set SHARE PERMISSIONS which are different from NTFS Permissions. Share Permissions allow you to tell WHO on your NETWORK can See, Read or Write you shared folder. NTFS permissions defines who can ACCESS your shared folder.
e.g: If you set permissions of User A to Write in your shared folder, and then on the NTFS permissions you deny him, he will see the folder but won't be able to Write into it !
Hope this answered your question.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
i.e. Grant connect on SQL server installed on Windows 2003B and Windows 2003A
And Grant db_backupoperator SQL server installed on Windows 2003A and file permissions on E and F drives of Windows 2003A.