5

I am running into an issue when trying to script the creation of a credential and related proxy in Microsoft SQL Server Management Studio.

My script is the following:

CREATE CREDENTIAL xxx WITH IDENTITY = 'domain\xxx', SECRET = '*******';

EXEC msdb.dbo.sp_add_proxy @proxy_name  = 'yyy' 
,@enabled = 1 
,@description = 'description here'
,@credential_name = 'xxx'

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name = 'yyy'
,@subsystem_id = 9;

This creates both the credential and the proxy.

HOWEVER, it fails to add the credential correctly to the proxy. When trying to manually (with the UI) add the credential I receive the following error:

Alter failed for ProxyAccount 'yyy' (Microsoft.SqlServer.Smo) Additional Information: Object reference not set to an instance of an object. (Microsoft.SqlServer.Smo)

Attempt to add credential

Error message

I have tried dropping the credential and re-adding it with no results. I've tried to drop and re-add the proxy but I receive the following error:

The credential name for the proxy is not defined. (SqlManagerUI)

Which goes back to not being able to assign a credential to that proxy.

Devon_C_Miller
  • 16,248
  • 3
  • 45
  • 71
Cody
  • 161
  • 7

2 Answers2

3

I found this link that appears to fix the issue. I'm not really sure how it works but my issue is gone.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ecabf8d5-5910-4c6a-90af-c4219a0c3418/msg-14529-on-spupdateproxy?forum=sqlsecurity

USE [msdb]
GO

EXEC msdb.dbo.sp_update_proxy @proxy_name = N'yyy'
    ,@credential_name = N'xxx'
    ,@description = N''
GO
Cody
  • 161
  • 7
0

In credential properties, for identity, click the build icon and then check names to make sure it is a valid name.

Dyonn
  • 11
  • 1