SQL Server Configuration Manager Error The Parameter is Invalid

By: Sergey Gigoyan | Updated: 2021-06-11 | Comments | Related: > SQL Server Configurations

Problem

In a previous article, we learned how to migrate the master database files to a new location. This process assumes changing the SQL Server startup parameters in SQL Server Configuration Manager and setting the new paths of the data and log files. This is quite a simple process, and everything should go smoothly. While testing this process, I ran into an issue with changing the startup parameters in SQL Server Configuration Manager and getting the error "The parameter is invalid". I followed the same steps that worked for other SQL Server instances, but was now getting this error.

In this article, we will describe what caused this issue and how this can be solved.

Solution

To make the solution more informative and clearer, we will define a sample task to reproduce the issue and solve this problem.

I was following the steps in the Move SQL Server Files for Master Database article. I moved the master database files to the location shown below C:\MSSQL\Master-Databases\.

When I got to the point of updating the startup parameters, I recieved the following error message.

After a bunch of trial and error, I figured out the reason for this is that the new path contains a hyphen which is a special character and is rejected by SQL Server Configuration Manager. I could have just changed the folder and moved the master database files to another folder, but I wanted to see if there was a way to solve this problem.

Realizing that it is impossible to change this parameter via SQL Server Configuration Manager we need to switch to alternative options. Specifically, we will set the values using the Windows registry. First, we need to find out which registry keys and which arguments we should edit to change these values. To do that, we will run the code below:

SELECT * FROM sys.dm_server_registry
WHERE registry_key LIKE '%MSSQLSERVER\Parameters%'

The result clearly shows the registry paths of these startup parameters:

Now, we will go to the Registry editor and will locate and edit these values. Just type "regedit" in the Windows search and the Registry Editor will appear which we can click on to open. We can also open the Registry Editor by issuing the regedit command in the Windows Command Prompt:

When we are in the registry editor, we can locate the path which the query above shows. In our case: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer\Parameters

Here we can see that SQLArg0 and SQLArg2 values contain the master database data and log files correspondingly:

We will change these values to the new path for both parameters, which works without any issues.

Now we can try to start SQL Server via SQL Server Configuration Manager and can see that it starts successfully:

By running the query below, we can find the new paths of the master database files:

SELECT name AS FileLogicalName, physical_name AS FileLocation
FROM sys.master_files  
WHERE database_id = 1

Perfect, the new path with a hyphen is set:

The same path we can see in the SQL Server Configuration Manager:

A bit tricky situation: while there is no way to directly set a parameter with a special symbol (hyphen in our case) in SQL Server Configuration Manager, the new path with the symbol appears in Configuration Manager after setting it through the Registry Editor. Anyway, our task is solved and the new path containing a special symbol is set.

Before concluding, let’s do one more test. It will be interesting to see what happens if we try to edit this path in SQL Server Configuration Manager. Will it allow us to keep the current path? Let's see. We will just click on the startup parameter with a hyphen and without changing anything click "Update". Hmm, the same error appears:

Even though the path with a hyphen appears here in Configuration Manager, the error occurs in saving the same value. We will just click "OK" and close the window. Our path remains with a hyphen as we haven’t changed anything.

Conclusion

In conclusion, although it is not possible to specify the SQL Server startup parameters containing special characters in the SQL Server Configuration Manager, it is still possible to overcome this obstacle. Startup parameters with special characters can be set directly in the Windows registry editor by editing the values of the corresponding arguments. A better option is to try to avoid this situation and now use characters that will cause an issue.

Next Steps

Please find more topic-related information by following the links below:

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2021-06-11