How do I configure SQL Server 2012 so that it can restore and see files in my user account?












10















I have an SQL Server 2012 instance running as a service on my computer, and according to the service page it logs on as account "NT ServiceMSSQLSERVER". However I can't see that account name anywhere else including in the "Local Users and Groups" area in the computer management screen, because, as a link below says, that's not a User Account, it's a service name, in that box that Microsoft so helpfully labelled "account". At this point I can see many people being confused.



The task I'm trying to accomplish is to restore files using the SSMS dialog "Locate Backup File", which uses a dialog completely unlike any of the standard windows file open dialogs, probably because it's doing a "remote" job and operating from the security context of the SQL server, another rich source of end user confusion, that I hope this question might help clear up.



So far if I want to restore a backup .mdf/.bak file that I have in one of my folders, I have to set that folder to readable by Everyone or else I can't get in there with the SQL Server "Locate Backup File" window. I find this idea that you're using a GUI talking to a service that has different user accounts and rights than you, that nobody at Microsoft even cared to make clear to you, very confusing even when I have years of experience with windows system administration.



I'm hoping I missed some documentation pages for SQL Server that would tell you, after installing a new SQL server instance, how you might set up security.



Forum posts like this one have even Microsoft staffers saying "this is complicated", and that it "changed, again" in Denali. How does this work now in SQL Server 2012, and how might I add permission to read files belonging to Users to the SQL Database engine's security SID.










share|improve this question























  • The reason that SQL doesn't use the standard dialog is so you can't pick a file that SQL can't read from. As to how to configure security, are you open to changing the account that SQL runs under or do you want to leave it the way that it is now? If changing the service account is okay then this is easy to take care of. We use named accounts so I can't do testing with the generic MSSQLSERVER account to help with that.

    – cfradenburg
    Dec 18 '12 at 17:09






  • 1





    See here: dba.stackexchange.com/questions/23864/…

    – Jon Seigel
    Dec 18 '12 at 18:01











  • If everybody changes their SQL Servers to run with a regular user ID when doing development, that might be the "best practice". I'm just wondering if that's a good idea or not. If manually adding "NT ServiceMSSQLSERVER" to the folders I need to add to is the standard idea, I guess that would be the most logical minimal fix, right? Wot I wish is that the SQL server SSMS UI was a little easier to connect to this hidden "NT Serverxxx" account id.

    – Warren P
    Dec 18 '12 at 18:50


















10















I have an SQL Server 2012 instance running as a service on my computer, and according to the service page it logs on as account "NT ServiceMSSQLSERVER". However I can't see that account name anywhere else including in the "Local Users and Groups" area in the computer management screen, because, as a link below says, that's not a User Account, it's a service name, in that box that Microsoft so helpfully labelled "account". At this point I can see many people being confused.



The task I'm trying to accomplish is to restore files using the SSMS dialog "Locate Backup File", which uses a dialog completely unlike any of the standard windows file open dialogs, probably because it's doing a "remote" job and operating from the security context of the SQL server, another rich source of end user confusion, that I hope this question might help clear up.



So far if I want to restore a backup .mdf/.bak file that I have in one of my folders, I have to set that folder to readable by Everyone or else I can't get in there with the SQL Server "Locate Backup File" window. I find this idea that you're using a GUI talking to a service that has different user accounts and rights than you, that nobody at Microsoft even cared to make clear to you, very confusing even when I have years of experience with windows system administration.



I'm hoping I missed some documentation pages for SQL Server that would tell you, after installing a new SQL server instance, how you might set up security.



Forum posts like this one have even Microsoft staffers saying "this is complicated", and that it "changed, again" in Denali. How does this work now in SQL Server 2012, and how might I add permission to read files belonging to Users to the SQL Database engine's security SID.










share|improve this question























  • The reason that SQL doesn't use the standard dialog is so you can't pick a file that SQL can't read from. As to how to configure security, are you open to changing the account that SQL runs under or do you want to leave it the way that it is now? If changing the service account is okay then this is easy to take care of. We use named accounts so I can't do testing with the generic MSSQLSERVER account to help with that.

    – cfradenburg
    Dec 18 '12 at 17:09






  • 1





    See here: dba.stackexchange.com/questions/23864/…

    – Jon Seigel
    Dec 18 '12 at 18:01











  • If everybody changes their SQL Servers to run with a regular user ID when doing development, that might be the "best practice". I'm just wondering if that's a good idea or not. If manually adding "NT ServiceMSSQLSERVER" to the folders I need to add to is the standard idea, I guess that would be the most logical minimal fix, right? Wot I wish is that the SQL server SSMS UI was a little easier to connect to this hidden "NT Serverxxx" account id.

    – Warren P
    Dec 18 '12 at 18:50
















10












10








10


4






I have an SQL Server 2012 instance running as a service on my computer, and according to the service page it logs on as account "NT ServiceMSSQLSERVER". However I can't see that account name anywhere else including in the "Local Users and Groups" area in the computer management screen, because, as a link below says, that's not a User Account, it's a service name, in that box that Microsoft so helpfully labelled "account". At this point I can see many people being confused.



The task I'm trying to accomplish is to restore files using the SSMS dialog "Locate Backup File", which uses a dialog completely unlike any of the standard windows file open dialogs, probably because it's doing a "remote" job and operating from the security context of the SQL server, another rich source of end user confusion, that I hope this question might help clear up.



So far if I want to restore a backup .mdf/.bak file that I have in one of my folders, I have to set that folder to readable by Everyone or else I can't get in there with the SQL Server "Locate Backup File" window. I find this idea that you're using a GUI talking to a service that has different user accounts and rights than you, that nobody at Microsoft even cared to make clear to you, very confusing even when I have years of experience with windows system administration.



I'm hoping I missed some documentation pages for SQL Server that would tell you, after installing a new SQL server instance, how you might set up security.



Forum posts like this one have even Microsoft staffers saying "this is complicated", and that it "changed, again" in Denali. How does this work now in SQL Server 2012, and how might I add permission to read files belonging to Users to the SQL Database engine's security SID.










share|improve this question














I have an SQL Server 2012 instance running as a service on my computer, and according to the service page it logs on as account "NT ServiceMSSQLSERVER". However I can't see that account name anywhere else including in the "Local Users and Groups" area in the computer management screen, because, as a link below says, that's not a User Account, it's a service name, in that box that Microsoft so helpfully labelled "account". At this point I can see many people being confused.



The task I'm trying to accomplish is to restore files using the SSMS dialog "Locate Backup File", which uses a dialog completely unlike any of the standard windows file open dialogs, probably because it's doing a "remote" job and operating from the security context of the SQL server, another rich source of end user confusion, that I hope this question might help clear up.



So far if I want to restore a backup .mdf/.bak file that I have in one of my folders, I have to set that folder to readable by Everyone or else I can't get in there with the SQL Server "Locate Backup File" window. I find this idea that you're using a GUI talking to a service that has different user accounts and rights than you, that nobody at Microsoft even cared to make clear to you, very confusing even when I have years of experience with windows system administration.



I'm hoping I missed some documentation pages for SQL Server that would tell you, after installing a new SQL server instance, how you might set up security.



Forum posts like this one have even Microsoft staffers saying "this is complicated", and that it "changed, again" in Denali. How does this work now in SQL Server 2012, and how might I add permission to read files belonging to Users to the SQL Database engine's security SID.







sql-server security






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Dec 18 '12 at 14:32









Warren PWarren P

60211024




60211024













  • The reason that SQL doesn't use the standard dialog is so you can't pick a file that SQL can't read from. As to how to configure security, are you open to changing the account that SQL runs under or do you want to leave it the way that it is now? If changing the service account is okay then this is easy to take care of. We use named accounts so I can't do testing with the generic MSSQLSERVER account to help with that.

    – cfradenburg
    Dec 18 '12 at 17:09






  • 1





    See here: dba.stackexchange.com/questions/23864/…

    – Jon Seigel
    Dec 18 '12 at 18:01











  • If everybody changes their SQL Servers to run with a regular user ID when doing development, that might be the "best practice". I'm just wondering if that's a good idea or not. If manually adding "NT ServiceMSSQLSERVER" to the folders I need to add to is the standard idea, I guess that would be the most logical minimal fix, right? Wot I wish is that the SQL server SSMS UI was a little easier to connect to this hidden "NT Serverxxx" account id.

    – Warren P
    Dec 18 '12 at 18:50





















  • The reason that SQL doesn't use the standard dialog is so you can't pick a file that SQL can't read from. As to how to configure security, are you open to changing the account that SQL runs under or do you want to leave it the way that it is now? If changing the service account is okay then this is easy to take care of. We use named accounts so I can't do testing with the generic MSSQLSERVER account to help with that.

    – cfradenburg
    Dec 18 '12 at 17:09






  • 1





    See here: dba.stackexchange.com/questions/23864/…

    – Jon Seigel
    Dec 18 '12 at 18:01











  • If everybody changes their SQL Servers to run with a regular user ID when doing development, that might be the "best practice". I'm just wondering if that's a good idea or not. If manually adding "NT ServiceMSSQLSERVER" to the folders I need to add to is the standard idea, I guess that would be the most logical minimal fix, right? Wot I wish is that the SQL server SSMS UI was a little easier to connect to this hidden "NT Serverxxx" account id.

    – Warren P
    Dec 18 '12 at 18:50



















The reason that SQL doesn't use the standard dialog is so you can't pick a file that SQL can't read from. As to how to configure security, are you open to changing the account that SQL runs under or do you want to leave it the way that it is now? If changing the service account is okay then this is easy to take care of. We use named accounts so I can't do testing with the generic MSSQLSERVER account to help with that.

– cfradenburg
Dec 18 '12 at 17:09





The reason that SQL doesn't use the standard dialog is so you can't pick a file that SQL can't read from. As to how to configure security, are you open to changing the account that SQL runs under or do you want to leave it the way that it is now? If changing the service account is okay then this is easy to take care of. We use named accounts so I can't do testing with the generic MSSQLSERVER account to help with that.

– cfradenburg
Dec 18 '12 at 17:09




1




1





See here: dba.stackexchange.com/questions/23864/…

– Jon Seigel
Dec 18 '12 at 18:01





See here: dba.stackexchange.com/questions/23864/…

– Jon Seigel
Dec 18 '12 at 18:01













If everybody changes their SQL Servers to run with a regular user ID when doing development, that might be the "best practice". I'm just wondering if that's a good idea or not. If manually adding "NT ServiceMSSQLSERVER" to the folders I need to add to is the standard idea, I guess that would be the most logical minimal fix, right? Wot I wish is that the SQL server SSMS UI was a little easier to connect to this hidden "NT Serverxxx" account id.

– Warren P
Dec 18 '12 at 18:50







If everybody changes their SQL Servers to run with a regular user ID when doing development, that might be the "best practice". I'm just wondering if that's a good idea or not. If manually adding "NT ServiceMSSQLSERVER" to the folders I need to add to is the standard idea, I guess that would be the most logical minimal fix, right? Wot I wish is that the SQL server SSMS UI was a little easier to connect to this hidden "NT Serverxxx" account id.

– Warren P
Dec 18 '12 at 18:50












4 Answers
4






active

oldest

votes


















5














For reference "Denali" is SQL Server 2012. With regards to "end user confusion", I am not all that concerned with whether a end user is confused or not with regards to SSMS. Microsoft did not develop this tool for the normal end user, but for the database administrator and/or a user that had to manage a database. Therefore there will be a learning curve with the tools provided and how they function. The file dialog box has been that way in SSMS since SSMS came out with SQL Server 2005. This is why you will general see most stick with T-SQL statements for backing up, restoring, or attaching a database that have been using it since then.



To configure file system permissions with SQL Server you can follow the instructions from MSDN here.



The way service accounts are handled did not come with or because of SQL Server, it was due to the change on the operating system level. Window Server 2008 R2 put a bit more of a security layer around service accounts. The advantage you have is that the service account can more easily access resources on a domain even if installed with the default settings. This link provides a pretty detailed look at how service account permissions are handled with SQL Server 2012. Excerpt from the link is below on the Virtual Accounts used by default in SQL Server 2012. There is also a link provided in the article that goes into more discussion on the Service Account concept with Windows, here. It is from Window Server 2008 R2 but I believe still holds true on Window Server 2012, and likely Window Server 2012 R2.




Virtual Accounts



Virtual accounts in Windows Server 2008 R2 and Windows 7 are managed
local accounts that provide the following features to simplify service
administration. The virtual account is auto-managed, and the virtual
account can access the network in a domain environment. If the default
value is used for the service accounts during SQL Server setup on
Windows Server 2008 R2 or Windows 7, a virtual account using the
instance name as the service name is used, in the format NT
SERVICE. Services that run as virtual accounts access
network resources by using the credentials of the computer account in
the format $. When specifying a virtual
account to start SQL Server, leave the password blank. If the virtual
account fails to register the Service Principal Name (SPN), register
the SPN manually. For more information on registering a SPN manually,
see Register a Service Principal Name for Kerberos Connections.
Note Note
Virtual accounts cannot be used for SQL Server Failover Cluster Instance, because the virtual account would not have the same SID on each node of the cluster.



The following table lists examples of virtual account names.



Default instance of the Database Engine service: NT SERVICEMSSQLSERVER
Named instance of a Database Engine service named PAYROLL: NT SERVICEMSSQL$PAYROLL
SQL Server Agent service on the default instance of SQL Server: NT SERVICESQLSERVERAGENT
SQL Server Agent service on an instance of SQL Server named PAYROLL: NT SERVICESQLAGENT$PAYROLL







share|improve this answer



















  • 3





    By end user I mean any of the dozens of categories of people, some quite professional and technically competent in various disciplines, but who may NOT be DBAs. Personally I think even DBAs would find that the UI on this tool could do to be improved. The robust third party tool market is another indicator that SSMS, while free, is entirely worth the price.

    – Warren P
    Apr 7 '14 at 13:09






  • 1





    In 2017 it hasn't gotten any better. So the short answer is "it's never going to get better, learn how it works, however poorly the UI is designed".

    – Warren P
    Jan 22 '17 at 14:36



















2














To see/access the files in another folder, give NT SERVICEMSSQLSERVER permissions on the folder. See my answer posted on .bak file not visible in any directory in SSMS for screen shots and the steps involved. (Slightly different from adding normal user/group permissions to a folder.)



Hope that helps!






share|improve this answer

































    1














    In fact, if you try to do a restore via SSMS, and you are logged in via Windows Authentication (!), it will be YOUR Windows account (and not "everyone either) that needs the permissions to that - NOT the SQL Server Service Account.
    Hope that clears the confusion.
    The service account has other needs.
    For example if you were logged in via a SQL Authenticated Account - then which permissions should the operating system ask for? - It will be the Service Account of SQL Server in this case only!
    This has not changed since SQL Server 7.0 and probably not even before that :)






    share|improve this answer



















    • 1





      Don't forget about credentials.

      – Remus Rusanu
      Aug 27 '13 at 16:53











    • I'm not sure it clears the confusion... If you're logged on via WA, your SQL Server account needs the permissions to restore (as far as SQL Server is concerned), but the SQL Server service account needs the read permissions on the file system to open the .bak file (which it generally won't have if it's in your user directory, unless explicitly granted).

      – Bruno
      Jan 28 '15 at 0:05



















    -1














    I just laugh at the insane rocket science. Microsoft technologies are becoming so complicated they're almost useless. 'No wonder so many other tech stacks have become popular.






    share|improve this answer








    New contributor




    user174696 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
    Check out our Code of Conduct.




















      Your Answer








      StackExchange.ready(function() {
      var channelOptions = {
      tags: "".split(" "),
      id: "182"
      };
      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: false,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: null,
      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
      });


      }
      });














      draft saved

      draft discarded


















      StackExchange.ready(
      function () {
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f30668%2fhow-do-i-configure-sql-server-2012-so-that-it-can-restore-and-see-files-in-my-us%23new-answer', 'question_page');
      }
      );

      Post as a guest















      Required, but never shown

























      4 Answers
      4






      active

      oldest

      votes








      4 Answers
      4






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









      5














      For reference "Denali" is SQL Server 2012. With regards to "end user confusion", I am not all that concerned with whether a end user is confused or not with regards to SSMS. Microsoft did not develop this tool for the normal end user, but for the database administrator and/or a user that had to manage a database. Therefore there will be a learning curve with the tools provided and how they function. The file dialog box has been that way in SSMS since SSMS came out with SQL Server 2005. This is why you will general see most stick with T-SQL statements for backing up, restoring, or attaching a database that have been using it since then.



      To configure file system permissions with SQL Server you can follow the instructions from MSDN here.



      The way service accounts are handled did not come with or because of SQL Server, it was due to the change on the operating system level. Window Server 2008 R2 put a bit more of a security layer around service accounts. The advantage you have is that the service account can more easily access resources on a domain even if installed with the default settings. This link provides a pretty detailed look at how service account permissions are handled with SQL Server 2012. Excerpt from the link is below on the Virtual Accounts used by default in SQL Server 2012. There is also a link provided in the article that goes into more discussion on the Service Account concept with Windows, here. It is from Window Server 2008 R2 but I believe still holds true on Window Server 2012, and likely Window Server 2012 R2.




      Virtual Accounts



      Virtual accounts in Windows Server 2008 R2 and Windows 7 are managed
      local accounts that provide the following features to simplify service
      administration. The virtual account is auto-managed, and the virtual
      account can access the network in a domain environment. If the default
      value is used for the service accounts during SQL Server setup on
      Windows Server 2008 R2 or Windows 7, a virtual account using the
      instance name as the service name is used, in the format NT
      SERVICE. Services that run as virtual accounts access
      network resources by using the credentials of the computer account in
      the format $. When specifying a virtual
      account to start SQL Server, leave the password blank. If the virtual
      account fails to register the Service Principal Name (SPN), register
      the SPN manually. For more information on registering a SPN manually,
      see Register a Service Principal Name for Kerberos Connections.
      Note Note
      Virtual accounts cannot be used for SQL Server Failover Cluster Instance, because the virtual account would not have the same SID on each node of the cluster.



      The following table lists examples of virtual account names.



      Default instance of the Database Engine service: NT SERVICEMSSQLSERVER
      Named instance of a Database Engine service named PAYROLL: NT SERVICEMSSQL$PAYROLL
      SQL Server Agent service on the default instance of SQL Server: NT SERVICESQLSERVERAGENT
      SQL Server Agent service on an instance of SQL Server named PAYROLL: NT SERVICESQLAGENT$PAYROLL







      share|improve this answer



















      • 3





        By end user I mean any of the dozens of categories of people, some quite professional and technically competent in various disciplines, but who may NOT be DBAs. Personally I think even DBAs would find that the UI on this tool could do to be improved. The robust third party tool market is another indicator that SSMS, while free, is entirely worth the price.

        – Warren P
        Apr 7 '14 at 13:09






      • 1





        In 2017 it hasn't gotten any better. So the short answer is "it's never going to get better, learn how it works, however poorly the UI is designed".

        – Warren P
        Jan 22 '17 at 14:36
















      5














      For reference "Denali" is SQL Server 2012. With regards to "end user confusion", I am not all that concerned with whether a end user is confused or not with regards to SSMS. Microsoft did not develop this tool for the normal end user, but for the database administrator and/or a user that had to manage a database. Therefore there will be a learning curve with the tools provided and how they function. The file dialog box has been that way in SSMS since SSMS came out with SQL Server 2005. This is why you will general see most stick with T-SQL statements for backing up, restoring, or attaching a database that have been using it since then.



      To configure file system permissions with SQL Server you can follow the instructions from MSDN here.



      The way service accounts are handled did not come with or because of SQL Server, it was due to the change on the operating system level. Window Server 2008 R2 put a bit more of a security layer around service accounts. The advantage you have is that the service account can more easily access resources on a domain even if installed with the default settings. This link provides a pretty detailed look at how service account permissions are handled with SQL Server 2012. Excerpt from the link is below on the Virtual Accounts used by default in SQL Server 2012. There is also a link provided in the article that goes into more discussion on the Service Account concept with Windows, here. It is from Window Server 2008 R2 but I believe still holds true on Window Server 2012, and likely Window Server 2012 R2.




      Virtual Accounts



      Virtual accounts in Windows Server 2008 R2 and Windows 7 are managed
      local accounts that provide the following features to simplify service
      administration. The virtual account is auto-managed, and the virtual
      account can access the network in a domain environment. If the default
      value is used for the service accounts during SQL Server setup on
      Windows Server 2008 R2 or Windows 7, a virtual account using the
      instance name as the service name is used, in the format NT
      SERVICE. Services that run as virtual accounts access
      network resources by using the credentials of the computer account in
      the format $. When specifying a virtual
      account to start SQL Server, leave the password blank. If the virtual
      account fails to register the Service Principal Name (SPN), register
      the SPN manually. For more information on registering a SPN manually,
      see Register a Service Principal Name for Kerberos Connections.
      Note Note
      Virtual accounts cannot be used for SQL Server Failover Cluster Instance, because the virtual account would not have the same SID on each node of the cluster.



      The following table lists examples of virtual account names.



      Default instance of the Database Engine service: NT SERVICEMSSQLSERVER
      Named instance of a Database Engine service named PAYROLL: NT SERVICEMSSQL$PAYROLL
      SQL Server Agent service on the default instance of SQL Server: NT SERVICESQLSERVERAGENT
      SQL Server Agent service on an instance of SQL Server named PAYROLL: NT SERVICESQLAGENT$PAYROLL







      share|improve this answer



















      • 3





        By end user I mean any of the dozens of categories of people, some quite professional and technically competent in various disciplines, but who may NOT be DBAs. Personally I think even DBAs would find that the UI on this tool could do to be improved. The robust third party tool market is another indicator that SSMS, while free, is entirely worth the price.

        – Warren P
        Apr 7 '14 at 13:09






      • 1





        In 2017 it hasn't gotten any better. So the short answer is "it's never going to get better, learn how it works, however poorly the UI is designed".

        – Warren P
        Jan 22 '17 at 14:36














      5












      5








      5







      For reference "Denali" is SQL Server 2012. With regards to "end user confusion", I am not all that concerned with whether a end user is confused or not with regards to SSMS. Microsoft did not develop this tool for the normal end user, but for the database administrator and/or a user that had to manage a database. Therefore there will be a learning curve with the tools provided and how they function. The file dialog box has been that way in SSMS since SSMS came out with SQL Server 2005. This is why you will general see most stick with T-SQL statements for backing up, restoring, or attaching a database that have been using it since then.



      To configure file system permissions with SQL Server you can follow the instructions from MSDN here.



      The way service accounts are handled did not come with or because of SQL Server, it was due to the change on the operating system level. Window Server 2008 R2 put a bit more of a security layer around service accounts. The advantage you have is that the service account can more easily access resources on a domain even if installed with the default settings. This link provides a pretty detailed look at how service account permissions are handled with SQL Server 2012. Excerpt from the link is below on the Virtual Accounts used by default in SQL Server 2012. There is also a link provided in the article that goes into more discussion on the Service Account concept with Windows, here. It is from Window Server 2008 R2 but I believe still holds true on Window Server 2012, and likely Window Server 2012 R2.




      Virtual Accounts



      Virtual accounts in Windows Server 2008 R2 and Windows 7 are managed
      local accounts that provide the following features to simplify service
      administration. The virtual account is auto-managed, and the virtual
      account can access the network in a domain environment. If the default
      value is used for the service accounts during SQL Server setup on
      Windows Server 2008 R2 or Windows 7, a virtual account using the
      instance name as the service name is used, in the format NT
      SERVICE. Services that run as virtual accounts access
      network resources by using the credentials of the computer account in
      the format $. When specifying a virtual
      account to start SQL Server, leave the password blank. If the virtual
      account fails to register the Service Principal Name (SPN), register
      the SPN manually. For more information on registering a SPN manually,
      see Register a Service Principal Name for Kerberos Connections.
      Note Note
      Virtual accounts cannot be used for SQL Server Failover Cluster Instance, because the virtual account would not have the same SID on each node of the cluster.



      The following table lists examples of virtual account names.



      Default instance of the Database Engine service: NT SERVICEMSSQLSERVER
      Named instance of a Database Engine service named PAYROLL: NT SERVICEMSSQL$PAYROLL
      SQL Server Agent service on the default instance of SQL Server: NT SERVICESQLSERVERAGENT
      SQL Server Agent service on an instance of SQL Server named PAYROLL: NT SERVICESQLAGENT$PAYROLL







      share|improve this answer













      For reference "Denali" is SQL Server 2012. With regards to "end user confusion", I am not all that concerned with whether a end user is confused or not with regards to SSMS. Microsoft did not develop this tool for the normal end user, but for the database administrator and/or a user that had to manage a database. Therefore there will be a learning curve with the tools provided and how they function. The file dialog box has been that way in SSMS since SSMS came out with SQL Server 2005. This is why you will general see most stick with T-SQL statements for backing up, restoring, or attaching a database that have been using it since then.



      To configure file system permissions with SQL Server you can follow the instructions from MSDN here.



      The way service accounts are handled did not come with or because of SQL Server, it was due to the change on the operating system level. Window Server 2008 R2 put a bit more of a security layer around service accounts. The advantage you have is that the service account can more easily access resources on a domain even if installed with the default settings. This link provides a pretty detailed look at how service account permissions are handled with SQL Server 2012. Excerpt from the link is below on the Virtual Accounts used by default in SQL Server 2012. There is also a link provided in the article that goes into more discussion on the Service Account concept with Windows, here. It is from Window Server 2008 R2 but I believe still holds true on Window Server 2012, and likely Window Server 2012 R2.




      Virtual Accounts



      Virtual accounts in Windows Server 2008 R2 and Windows 7 are managed
      local accounts that provide the following features to simplify service
      administration. The virtual account is auto-managed, and the virtual
      account can access the network in a domain environment. If the default
      value is used for the service accounts during SQL Server setup on
      Windows Server 2008 R2 or Windows 7, a virtual account using the
      instance name as the service name is used, in the format NT
      SERVICE. Services that run as virtual accounts access
      network resources by using the credentials of the computer account in
      the format $. When specifying a virtual
      account to start SQL Server, leave the password blank. If the virtual
      account fails to register the Service Principal Name (SPN), register
      the SPN manually. For more information on registering a SPN manually,
      see Register a Service Principal Name for Kerberos Connections.
      Note Note
      Virtual accounts cannot be used for SQL Server Failover Cluster Instance, because the virtual account would not have the same SID on each node of the cluster.



      The following table lists examples of virtual account names.



      Default instance of the Database Engine service: NT SERVICEMSSQLSERVER
      Named instance of a Database Engine service named PAYROLL: NT SERVICEMSSQL$PAYROLL
      SQL Server Agent service on the default instance of SQL Server: NT SERVICESQLSERVERAGENT
      SQL Server Agent service on an instance of SQL Server named PAYROLL: NT SERVICESQLAGENT$PAYROLL








      share|improve this answer












      share|improve this answer



      share|improve this answer










      answered Oct 27 '13 at 1:17









      Shawn MeltonShawn Melton

      14.5k43882




      14.5k43882








      • 3





        By end user I mean any of the dozens of categories of people, some quite professional and technically competent in various disciplines, but who may NOT be DBAs. Personally I think even DBAs would find that the UI on this tool could do to be improved. The robust third party tool market is another indicator that SSMS, while free, is entirely worth the price.

        – Warren P
        Apr 7 '14 at 13:09






      • 1





        In 2017 it hasn't gotten any better. So the short answer is "it's never going to get better, learn how it works, however poorly the UI is designed".

        – Warren P
        Jan 22 '17 at 14:36














      • 3





        By end user I mean any of the dozens of categories of people, some quite professional and technically competent in various disciplines, but who may NOT be DBAs. Personally I think even DBAs would find that the UI on this tool could do to be improved. The robust third party tool market is another indicator that SSMS, while free, is entirely worth the price.

        – Warren P
        Apr 7 '14 at 13:09






      • 1





        In 2017 it hasn't gotten any better. So the short answer is "it's never going to get better, learn how it works, however poorly the UI is designed".

        – Warren P
        Jan 22 '17 at 14:36








      3




      3





      By end user I mean any of the dozens of categories of people, some quite professional and technically competent in various disciplines, but who may NOT be DBAs. Personally I think even DBAs would find that the UI on this tool could do to be improved. The robust third party tool market is another indicator that SSMS, while free, is entirely worth the price.

      – Warren P
      Apr 7 '14 at 13:09





      By end user I mean any of the dozens of categories of people, some quite professional and technically competent in various disciplines, but who may NOT be DBAs. Personally I think even DBAs would find that the UI on this tool could do to be improved. The robust third party tool market is another indicator that SSMS, while free, is entirely worth the price.

      – Warren P
      Apr 7 '14 at 13:09




      1




      1





      In 2017 it hasn't gotten any better. So the short answer is "it's never going to get better, learn how it works, however poorly the UI is designed".

      – Warren P
      Jan 22 '17 at 14:36





      In 2017 it hasn't gotten any better. So the short answer is "it's never going to get better, learn how it works, however poorly the UI is designed".

      – Warren P
      Jan 22 '17 at 14:36













      2














      To see/access the files in another folder, give NT SERVICEMSSQLSERVER permissions on the folder. See my answer posted on .bak file not visible in any directory in SSMS for screen shots and the steps involved. (Slightly different from adding normal user/group permissions to a folder.)



      Hope that helps!






      share|improve this answer






























        2














        To see/access the files in another folder, give NT SERVICEMSSQLSERVER permissions on the folder. See my answer posted on .bak file not visible in any directory in SSMS for screen shots and the steps involved. (Slightly different from adding normal user/group permissions to a folder.)



        Hope that helps!






        share|improve this answer




























          2












          2








          2







          To see/access the files in another folder, give NT SERVICEMSSQLSERVER permissions on the folder. See my answer posted on .bak file not visible in any directory in SSMS for screen shots and the steps involved. (Slightly different from adding normal user/group permissions to a folder.)



          Hope that helps!






          share|improve this answer















          To see/access the files in another folder, give NT SERVICEMSSQLSERVER permissions on the folder. See my answer posted on .bak file not visible in any directory in SSMS for screen shots and the steps involved. (Slightly different from adding normal user/group permissions to a folder.)



          Hope that helps!







          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Apr 13 '17 at 12:42









          Community

          1




          1










          answered Dec 12 '14 at 23:10









          AdamsTipsAdamsTips

          49643




          49643























              1














              In fact, if you try to do a restore via SSMS, and you are logged in via Windows Authentication (!), it will be YOUR Windows account (and not "everyone either) that needs the permissions to that - NOT the SQL Server Service Account.
              Hope that clears the confusion.
              The service account has other needs.
              For example if you were logged in via a SQL Authenticated Account - then which permissions should the operating system ask for? - It will be the Service Account of SQL Server in this case only!
              This has not changed since SQL Server 7.0 and probably not even before that :)






              share|improve this answer



















              • 1





                Don't forget about credentials.

                – Remus Rusanu
                Aug 27 '13 at 16:53











              • I'm not sure it clears the confusion... If you're logged on via WA, your SQL Server account needs the permissions to restore (as far as SQL Server is concerned), but the SQL Server service account needs the read permissions on the file system to open the .bak file (which it generally won't have if it's in your user directory, unless explicitly granted).

                – Bruno
                Jan 28 '15 at 0:05
















              1














              In fact, if you try to do a restore via SSMS, and you are logged in via Windows Authentication (!), it will be YOUR Windows account (and not "everyone either) that needs the permissions to that - NOT the SQL Server Service Account.
              Hope that clears the confusion.
              The service account has other needs.
              For example if you were logged in via a SQL Authenticated Account - then which permissions should the operating system ask for? - It will be the Service Account of SQL Server in this case only!
              This has not changed since SQL Server 7.0 and probably not even before that :)






              share|improve this answer



















              • 1





                Don't forget about credentials.

                – Remus Rusanu
                Aug 27 '13 at 16:53











              • I'm not sure it clears the confusion... If you're logged on via WA, your SQL Server account needs the permissions to restore (as far as SQL Server is concerned), but the SQL Server service account needs the read permissions on the file system to open the .bak file (which it generally won't have if it's in your user directory, unless explicitly granted).

                – Bruno
                Jan 28 '15 at 0:05














              1












              1








              1







              In fact, if you try to do a restore via SSMS, and you are logged in via Windows Authentication (!), it will be YOUR Windows account (and not "everyone either) that needs the permissions to that - NOT the SQL Server Service Account.
              Hope that clears the confusion.
              The service account has other needs.
              For example if you were logged in via a SQL Authenticated Account - then which permissions should the operating system ask for? - It will be the Service Account of SQL Server in this case only!
              This has not changed since SQL Server 7.0 and probably not even before that :)






              share|improve this answer













              In fact, if you try to do a restore via SSMS, and you are logged in via Windows Authentication (!), it will be YOUR Windows account (and not "everyone either) that needs the permissions to that - NOT the SQL Server Service Account.
              Hope that clears the confusion.
              The service account has other needs.
              For example if you were logged in via a SQL Authenticated Account - then which permissions should the operating system ask for? - It will be the Service Account of SQL Server in this case only!
              This has not changed since SQL Server 7.0 and probably not even before that :)







              share|improve this answer












              share|improve this answer



              share|improve this answer










              answered Aug 27 '13 at 16:46









              Andreas WolterAndreas Wolter

              1866




              1866








              • 1





                Don't forget about credentials.

                – Remus Rusanu
                Aug 27 '13 at 16:53











              • I'm not sure it clears the confusion... If you're logged on via WA, your SQL Server account needs the permissions to restore (as far as SQL Server is concerned), but the SQL Server service account needs the read permissions on the file system to open the .bak file (which it generally won't have if it's in your user directory, unless explicitly granted).

                – Bruno
                Jan 28 '15 at 0:05














              • 1





                Don't forget about credentials.

                – Remus Rusanu
                Aug 27 '13 at 16:53











              • I'm not sure it clears the confusion... If you're logged on via WA, your SQL Server account needs the permissions to restore (as far as SQL Server is concerned), but the SQL Server service account needs the read permissions on the file system to open the .bak file (which it generally won't have if it's in your user directory, unless explicitly granted).

                – Bruno
                Jan 28 '15 at 0:05








              1




              1





              Don't forget about credentials.

              – Remus Rusanu
              Aug 27 '13 at 16:53





              Don't forget about credentials.

              – Remus Rusanu
              Aug 27 '13 at 16:53













              I'm not sure it clears the confusion... If you're logged on via WA, your SQL Server account needs the permissions to restore (as far as SQL Server is concerned), but the SQL Server service account needs the read permissions on the file system to open the .bak file (which it generally won't have if it's in your user directory, unless explicitly granted).

              – Bruno
              Jan 28 '15 at 0:05





              I'm not sure it clears the confusion... If you're logged on via WA, your SQL Server account needs the permissions to restore (as far as SQL Server is concerned), but the SQL Server service account needs the read permissions on the file system to open the .bak file (which it generally won't have if it's in your user directory, unless explicitly granted).

              – Bruno
              Jan 28 '15 at 0:05











              -1














              I just laugh at the insane rocket science. Microsoft technologies are becoming so complicated they're almost useless. 'No wonder so many other tech stacks have become popular.






              share|improve this answer








              New contributor




              user174696 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
              Check out our Code of Conduct.

























                -1














                I just laugh at the insane rocket science. Microsoft technologies are becoming so complicated they're almost useless. 'No wonder so many other tech stacks have become popular.






                share|improve this answer








                New contributor




                user174696 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                Check out our Code of Conduct.























                  -1












                  -1








                  -1







                  I just laugh at the insane rocket science. Microsoft technologies are becoming so complicated they're almost useless. 'No wonder so many other tech stacks have become popular.






                  share|improve this answer








                  New contributor




                  user174696 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                  Check out our Code of Conduct.










                  I just laugh at the insane rocket science. Microsoft technologies are becoming so complicated they're almost useless. 'No wonder so many other tech stacks have become popular.







                  share|improve this answer








                  New contributor




                  user174696 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                  Check out our Code of Conduct.









                  share|improve this answer



                  share|improve this answer






                  New contributor




                  user174696 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                  Check out our Code of Conduct.









                  answered 12 mins ago









                  user174696user174696

                  1




                  1




                  New contributor




                  user174696 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                  Check out our Code of Conduct.





                  New contributor





                  user174696 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                  Check out our Code of Conduct.






                  user174696 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                  Check out our Code of Conduct.






























                      draft saved

                      draft discarded




















































                      Thanks for contributing an answer to Database Administrators Stack Exchange!


                      • 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.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function () {
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f30668%2fhow-do-i-configure-sql-server-2012-so-that-it-can-restore-and-see-files-in-my-us%23new-answer', 'question_page');
                      }
                      );

                      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







                      Popular posts from this blog

                      Liste der Baudenkmale in Friedland (Mecklenburg)

                      Single-Malt-Whisky

                      Czorneboh