xp_regread() returned error 5, 'Access is denied.'

Question

I'm running the SQL Server Copy Database Wizard.

Of note is that the Operator is NT AUTHORITY\SYSTEM, which I thought should have the authority to run whatever it wants.

How can we grant sufficient privileges to NT AUTHORITY\SYSTEM? I have already tried:

GRANT EXECUTE ON xp_regread TO public
GRANT EXECUTE ON xp_regread TO [NT AUTHORITY\SYSTEM]

And running the following shows that it worked.

SELECT
grantee_principal.name AS [Grantee]
, prmssn.permission_name
FROM
sys.all_objects AS xproc
INNER JOIN sys.database_permissions AS prmssn ON prmssn.major_id=xproc.object_id AND prmssn.minor_id=0 AND prmssn.class=1
INNER JOIN sys.database_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE
(xproc.type='X')and(xproc.name=N'xp_regread' and SCHEMA_NAME(xproc.schema_id)=N'sys')

Output:

Grantee                     permission_name
public                      EXECUTE
NT AUTHORITY\SYSTEM         EXECUTE

The following error occurs:

  Event Name: OnError
 Message: An exception occurred while executing a Transact-SQL statement or batch.
StackTrace:    at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand, SqlCommand& command)
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataReader(String query, SqlCommand& command)
   at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
   at Microsoft.SqlServer.Management.Smo.SqlServer.GetData(EnumResult erParent)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
   at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetInitDataReader(String[] fields, OrderBy[] orderby)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ImplInitialize(String[] fields, OrderBy[] orderby)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Initialize(Boolean allProperties)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.OnPropertyMissing(String propname, Boolean useDefaultValue)
   at Microsoft.SqlServer.Management.Smo.PropertyCollection.RetrieveProperty(Int32 index, Boolean useDefaultOnMissingValue)
   at Microsoft.SqlServer.Management.Smo.PropertyCollection.GetValueWithNullReplacement(String propertyName, Boolean throwOnNullValue, Boolean useDefaultOnMissingValue)
   at Microsoft.SqlServer.Management.Smo.Server.get_InstanceName()
   at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.MappedLogin.RefreshData()
   at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.MappedLogin.CheckDirty()
   at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.GetDatabaseLogins()
   at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.UpdateObjectsToCopy()
InnerException-->xp_regread() returned error 5, 'Access is denied.'
xp_regread() returned error 5, 'Access is denied.'
xp_regread() returned error 5, 'Access is denied.'
StackTrace:    at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand, SqlCommand& command)
 Operator: NT AUTHORITY\SYSTEM
 Source Name: SQLSERVER2008R2_Transfer Objects Task
 Source ID: {9D0562F4-FCC9-498D-A2A9-FC9E5F3B681B}
 Execution ID: {23FF505D-00D3-4F84-8B9D-D9EC962C78D2}
 Start Time: 2015-04-17 7:23:24 PM
 End Time: 2015-04-17 7:23:24 PM
 Data Code: 0

This is the tool we need to use, because we don't have access to the remote server, and because the Import-Export Wizard failed.

The `GRANT EXECUTE` statements grant execution rights to a user within SQL Server, it doesn't mean that underlying rights exist to read registry values. Could you verify that `NT AUTHORITY\System` login still has the `sysadmin` server role? — TT., Apr 18 '15 at 08:30
Also, from Microsofts docu on [Using the Copy Database Wizard](https://msdn.microsoft.com/en-us/library/ms188664%28v=sql.105%29.aspx), issues to consider: _You must be a member of the sysadmin fixed server role on both the source and destination servers._. So the login you specify on both source & destination needs to be a member of the sysadmin role. Please verify if they are member of the sysadmin role. — TT., Apr 18 '15 at 08:51
@TT As it turns out, I received the `xp_regread()` error when I had a medley of SQL Server tools installed (2005, 2008, 2012, 2014.) With that medley, I didn't need to have `sysadmin` privileges on the source. Instead, the Copy Database Wizard ran and threw the `xp_regread()` error after about 2 minutes. I found the error in the Event Viewer. When I removed all the SQL Server tools and installed only 2008 R2, the Copy Database Wizard blocked me after I chose the source, complaining that I lacked `sysadmin` on the source. The `xp_regread()` might be from something else. Thoughts? — Shaun Luttin, Apr 18 '15 at 22:35

score 2 · Answer 1 · answered Apr 18 '15 at 08:45

2

You might need to add the account to the sysadmin server role, which allows a member to perform every activity.

For 2008r2, execute this command:

EXEC sp_addsrvrolemember 'NT AUTHORITY\SYSTEM', 'sysadmin';

answered Apr 18 '15 at 08:45

RSax

328
3
12

Is there a way to check the before and after? – Adam Mendoza Nov 20 '18 at 05:49

score 2 · Answer 2 · answered Jun 21 '18 at 13:06

This is a common error encountered when attempting to update the registry from SQL Server, and there are some weird and non-obvious pathing issues in newer versions such as SQL Server 2017.

The error is not due to security within SQL Server, but instead Windows security related to the permissions on the registry keys as relates to the users under which specific SQL Server processes are running.

For instance, to execute MSSQL related registry commands from SQL Server, the system group NETWORKSERVICE needs full control on the relevant registry path. This is because SQL Agent (by default) runs as a user in that system group.

For SQL Server settings, the Registry path is: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server

As an example, the command in SQL Server to set the log file size limit to be 1024 KB is as follows:

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
 N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer', 
 N'ErrorLogSizeInKb', REG_DWORD, 1024
GO

Also note the truncated path above. The actual full registry path (for SQL2017) is as follows:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQLServer

The xp_instance_regwrite SQL Server command automatically injects the version key into the path. For SQL Server 2017 that key is MSSQL14.MSSQLSERVER.

xp_regread() returned error 5, 'Access is denied.'

2 Answers2

Linked