What registries does SQL server write?

During SQL server startup, the startup information will be read from the Registry first. Sometimes, when SQL Server cannot be started, we need to modify the registry. But where is the SQL server information stored in the registry? I have been searching for it from the Internet for a long time, but it is not comprehensive.

 

In fact, Microsoft added a new DMV in Versions later than 2008 R2 SP1 to query the configuration information and installation information in the Windows registry in the sqlserver instance. A line is returned for each registry entry, can return available on the host
The Network Configuration value of the SQL Server service or SQL server instance ..

The following are two examples:

The following example returns the network configuration information of the current SQL server instance.
Select registry_key, value_name, value_data from SYS. dm_server_registry where keyname like n' % supersocketnetlib % ';

The following example shows the parameters that are passed to the SQL server instance during startup.
Select registry_key, value_name, value_data from SYS. dm_server_registry where registry_key like n '% parameters ';

Some explanations about the registry value:

Parameter	OK to set?	Apparent usage
Agenterrorlogfile	Yes	Path where SQLAgent service puts its SQLAgent. Out log file.
Agentworkingdirectory	Yes	Path where SQLAgent stores temporary files. Possibly also default path for job step log files.
Backupdirectory	Yes	Path where Backup and Restore place disk = files by default.
Collation	No	Collation specified at installation.
CurrentVersion	No	SQL Server version (seems to be same as version ).
Dynamicports	Yes	Current dynamic port number for all IPs. Set to empty string ''to disable.
Edition	No	Edition of SQL server installed, (e.g. Standard Edition, Enterprise Edition ).
Errorlog	Yes	-E followed by path where SQL server writes errorlog files.
Fulltextdefaultpath	Yes	Path where full-text catalog files are kept .(?)
Masterdatafile	No *	-D followed by full path of master. MDF.
Masterlogfile	No *	-L followed by full path of mastlog. MDF
Patchlevel	No	Another flavor of version, though not as specific .(?)
Port	Yes	Static port number for all IPs.
Registeredowner	No	Owner specified at installation.
Sqlbinroot	No	Full path to the binn directory.
Sqldataroot	Yes	Path to the level above the data directory where new databases are created.
Sqlpath	No	Full path to MSSQL level of installation.
Sqlprogramdir	No	Full path to level above MSSQL. N installation directory.
Serialnumber	No	Apparently, serial number of installation. Blank on my installations.
Version	No	SQL Server version (seems to be same as CurrentVersion ).

After knowing the meaning of these values, we can modify the Authentication Mode of SQL Server, and find the registry.

HKLM \ SOFTWARE \ Microsoft \ microsoft sqlserver \ mssql.1 \ MSSQLServer \ loginmode

Then, change the value from 1 to 2. Restart SQL Server to see that the authentication mode has been changed.

For more information, see SYS. dm_server_registry (TRANSACT-SQL)