none
Giving SQL Sever access to connect to a a network share - no domain RRS feed

  • Question

  • I have a QNAP nas and I want to give my SQL Server 2019 access to a share on the server. The NAS is linux based and not part of a domain.

    I simply want to give my SQL server credentials to use when connecting to that share.

    How can I do this?

    I'm thinking it might be a proxy but I've never set one up before.

    Background: My stored procedures retrieves file metadata details on the server.  It is getting a permission error even though i have a Windows Credential setup to be able to access the share.   I can get to it no problem with windows explorer.

    Monday, February 3, 2020 8:20 PM

All replies

  • Is SQL Server running on the same machine as you are connectiing from? In thta case, I would expect it to work. SQL Server impersonates your Windows user to access the share. I'm here assuming that you are connecting with Windows authentication.

    If you are running your application on one machine and SQL Server runs on another, it is more difficult. For this Kerberos needs to be in the game, and whether it will in this particular case, I don't know. The key is that SQL Server must be trusted to impersonate you.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, February 3, 2020 10:38 PM
  • The SQL server is a different machine than the Linux based NAS. Both not on a domain.

    I only have a windows credential on the SQL server allowing access to  the share which works fine outside of SQL.

    The key question is how do you allow SQL to access files on another (linux) machine? 

    I'm not sure if i should be changing permission for the user that is used in the SQL service or looking at a SQL proxy or something else?

    I have an ID and password ready to go to allow this on the NAS -- i just don't know where to specify them in SQL.

    Tuesday, February 4, 2020 3:16 PM
  • Windows authentication outside of a domain can only work if password and username are the same on both machines. That's not really a good start. Here you have three machine and hope that SQL Server will be able to pass you username and password to the NAS.

    If you would log on to SQL Server with SQL authentication, access would be by the service account for SQL Server. In this case the service account would need to be a user on the NAS, and with the same password as on Windows. This also means, that the service account cannot be something like NT Service\MSSQLSERVER.

    This may have some chances when it comes to Windows shares. When it comes to Linux - I have no idea.

    The other alternative would be to lower the guard on the NAS and give access to anyone from the network.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, February 4, 2020 10:48 PM
  • Windows authentication outside of a domain can only work if password and username are the same on both machines. That's not really a good start. Here you have three machine and hope that SQL Server will be able to pass you username and password to the NAS.

    If you would log on to SQL Server with SQL authentication, access would be by the service account for SQL Server. In this case the service account would need to be a user on the NAS, and with the same password as on Windows. This also means, that the service account cannot be something like NT Service\MSSQLSERVER.

    This may have some chances when it comes to Windows shares. When it comes to Linux - I have no idea.

    The other alternative would be to lower the guard on the NAS and give access to anyone from the network.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thanks for this.  Originally i had my guard lowered like you mentioned but my NAS firmware is now requiring authentication as part of a firmware update.  I have no idea how to get the SQL to pass a credential.  If i change the login on the SQL server to something other than the default will i break something else?

    Saturday, February 15, 2020 2:01 AM
  • Thanks for this.  Originally i had my guard lowered like you mentioned but my NAS firmware is now requiring authentication as part of a firmware update.  I have no idea how to get the SQL to pass a credential.  If i change the login on the SQL server to something other than the default will i break something else?

    You mean, changing the service account for SQL Server? I don't think you break anything as long as you change the service account with SQL Server Configuration Manager. But if you set up a different account, you will to manage that password etc.

    Then again, that depends on what your environment is. If it is a home, it is possible that you can permit yourself to be more relaxed.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, February 15, 2020 10:49 AM