SQL Instance SMB Install Root

Question

We're trying to install an MS SQL stand-alone instance on an SMB share, following this documentation: install-sql-server-with-smb-fileshare-as-a-storage-option

However, as seen in the installation screenshots below, we are getting the error "The path is not on a specified drive Type."

I understand that the following path formats are not supported:

The below UNC path formats are not supported:

Loopback path, e.g., \localhost..\ or \127.0.0.1...\

Administrative shares, e.g., \servername\x$

Other UNC path formats like \?\x:\

Mapped network drives.

So, as seen in the second screenshot, I navigated directly to the share via the ... to the right of "Instance root directory", without mapping the share locally. However, we still get the "not on a specified drive Type" error. We also tried mapping the share, to fish for a different error message: as seen in the first screenshot, same result. We have tried mapping the share in numerous ways:

mount -o nolock -u:swssqlsa -p:xxx 192.168.xx.xx:/export/sol-ms_sql/db01 A:  
mount -o nolock 192.168.xx.xx:/export/sol-ms_sql/db01 A:  
New-PSDrive -Name "B" -Root "\\192.168.xx.xx\export\sol-ms_sql\db01" -PSProvider FileSystem  
New-PSDrive -Name "SOLMSSQLDB01" -Root "\\192.168.xx.xx\export\sol-ms_sql\db01" -PSProvider FileSystem  

We have tried running MS SQL's Setup.exe using the following parameters, as described in the documentation linked above:

.\SETUP.EXE /ACTION=Install /FEATURES=SQL /INSTANCENAME=SWSSQL /SQLSVCACCOUNT="swssqlegn" /SQLSVCPASSWORD="xxx" /SQLSYSADMINACCOUNTS="swssqlsa" /AGTSVCACCOUNT="swssqlagt" /AGTSVCPASSWORD="xxx" /INSTALLSQLDATADIR="\\192.168.xx.xx\export\sol-ms_sql\db01" /IACCEPTSQLSERVERLICENSETERMS  

.\SETUP.EXE /ACTION=Install /FEATURES=SQL /INSTANCENAME=SWSSQL /SQLSVCACCOUNT="swssqlegn" /SQLSVCPASSWORD="xxx" /SQLSYSADMINACCOUNTS="swssqlsa" /AGTSVCACCOUNT="swssqlagt" /AGTSVCPASSWORD="xxx" /INSTALLSQLDATADIR="SOLMSSQLDB01" /IACCEPTSQLSERVERLICENSETERMS  

.\SETUP.EXE /ACTION=Install /FEATURES=SQL /INSTANCENAME=SWSSQL /SQLSVCACCOUNT="swssqlegn" /SQLSVCPASSWORD="xxx" /SQLSYSADMINACCOUNTS="swssqlsa" /AGTSVCACCOUNT="swssqlagt" /AGTSVCPASSWORD="xxx" /INSTALLSQLDATADIR="\\KPRD-ZS7-H1-ADM\db01" /IACCEPTSQLSERVERLICENSETERMS  

However, the same error is always the same result.

Here are the results of running various Smb cmdlets:

New-SmbMapping -LocalPath 'Z:' -RemotePath '\\KPRD-ZS7-H1-ADM\db01'  
Get-SmbMapping  

Status Local Path Remote Path             
------ ---------- -----------             
OK     Z:         \\KPRD-ZS7-H1-ADM\db01  


Get-SmbConnection  

ServerName      ShareName UserName                      Credential                    Dialect NumOpens  
----------      --------- --------                      ----------                    ------- --------  
KPRD-ZS7-H1-ADM db01      KUTLPRDSOLSQL01\Administrator KUTLPRDSOLSQL01\Administrator 3.1.1   1         

Please let me know if you have any suggestions as to what I should check next, or possible reasons why this isn't working. This is a non-AD-joined machine, running all commands as local Administrator.

Thank you!

UPDATE - 20201211

@Cris Zhan-MSFT

Thanks for advising to specify a local location for the Feature Selection > Instance root directory field. I've done so and am able to move past that page without error.

I move on to the Database Engine Configuration > Data Directories tab. I enter \KPRD-ZS7-H1-ADM\db01 in the Data root directory field, and try hitting Next >. See the attached screenshot for the resultant error. I've also included a screenshot from the Server Configuration > Service Accounts page, where I specify the SQL Server Agent and SQL Server Database Engine accounts & passwords. I've also included a screenshot from the file server, where those same user accounts are shown as having full control over the share. When running this SQL Setup, I am doing it from the swssqlsa account.

I have also tried mapping the SMB share to a drive letter, using the swssqlsa account, and specifying that drive letter for the Data root directory field; I've attached a screenshot for that error too.

It seems the root of the problem are permissions from the file server side; but I'm not sure what is meant by the "SQL Server setup account" and it not having the SeSecurityPrivilege. What account is the "SQL Server setup account" (if it's not the Agent or Engine accounts)?

Thanks again.

Answer 1

Hi @Meyers, Martel ,

Please note that the documentation says：System databases and Database Engine user databases can be installed with SMB file server as a storage option.
This does not include all SQL Server files. SQL Server is a service so needs to be installed and run on a OS, just the database files can be stored in the SMB File Share or Shared Storage.

The specific installation options described in this document are:
1. In setup UI "Database Engine Configuration" page, "Data Directories" tab, set the parameter "Data root directory as "\fileserver1\share1".
2. In command prompt installation, specify the "/INSTALLSQLDATADIR" as "\fileserver1\share1".

I don't think you can specify the SMB File Share in SQL Server setup UI "Feature Selection" page — "Instance root directory" ,only local disk is allowed.

---

If the answer is helpful, please click "Accept Answer" and upvote it.

What can I do if my transaction log is full?--- Hot issues November
How to convert Profiler trace into a SQL Server table -- Hot issues November

Answer 2

Hi @Meyers, Martel ,

Sorry for my delay. It is indeed the account issue.

As pointed out by the error in the first screenshot, you need a domain account.
I also encountered this problem in the test, and then I used a domain account with local administrator rights and full permissions on the SMB share folder as the SQL Server agent and SQL Server engine account to solve the problem.
In addition, I use this domain account to log in to windows, and the domain account has sufficient permissions on the SMB share folder

>What account is the "SQL Server setup account" (if it's not the Agent or Engine accounts)?

The "SQL Server setup account" is the account used to log in to windows and run the sql server installation.

The account used to install SQL Server should have FULL CONTROL permissions on the SMB file share folder .
The account used to install SQL Server should be granted SeSecurityPrivilege privileges on the SMB file server. To grant this privilege, use the Local Security Policy console on the file server to add the SQL Server setup account to the Manage auditing and security log policy. This setting is available in the User Rights Assignments section under Local Policies in the Local Security Policy console.