Cannot use credentials for proxy account

Hi,

because my package does not run with SQL-Server-Agent, but without problems if started by "hand", I created a new credential which contains the information needed for the package. I did this as described on: http://msdn2.microsoft.com/en-us/library/ms190703.aspx .

After that i tried to create an proxy account, but when I chose the created credential, Management Studio says "Der Proxy "[name_of_credential] ist kein gültiger Windows-Benutzer(Microsoft SQL Server, Fehler: 14529)". This means something like: "This Proxy is not a valid windows-user. Error: 14529".

Any hints how to use a credential that is not a windows-user?

Regards,

Jan Wagner

You could try something like the following. It creates the Credential

and proxy. You need to decide what sub-system you want to run - I'm not

sure you'll be able to run the CmdExec subsystem, for example, with a

windows user.

-code start

--###################################################### describe

script

############################################################################

PRINT '>>> This script creates the LOGIN,

USER , CREDENTIALS and PROXY for INTEGRATION SERVICES SSIS PACKAGES

<<<'

PRINT '>>> This execution on server:

['+@.@.SERVERNAME+'] started at:

['+CONVERT(VARCHAR,CURRENT_TIMESTAMP,113)+'] <<<'

PRINT ''

PRINT '>>> Create Objects <<<'

PRINT ''

--################################################## Check and Drop

Existing

########################################################################

DECLARE

@.proxy_name SYSNAME,

@.subsystem_name SYSNAME,

@.UserName SYSNAME,

@.credential_name SYSNAME,

@.RowCount INT

SET @.credential_name = 'TestSQLUserCred'

SET @.proxy_name = 'TestSQLUserProxy'

SET @.subsystem_name = 'CmdExec'

SET @.UserName = 'TestSQLCredUser'

--################################################## CREATE Login

###################################################################################

SET NOCOUNT ON

USE [master]

IF NOT EXISTS (select 1 from sys.syslogins WHERE [Name] = @.UserName)

CREATE LOGIN TestSQLCredUser WITH Password = 'TestSQLCredUser1', DEFAULT_DATABASE = [IntegrationServices]

--################################################## CREATE User and

Grant Rights on DBS

############################################################

USE [IntegrationServices]

IF NOT EXISTS (select 1 from sys.sysusers WHERE [Name] = 'TestSQLCredUser')

CREATE USER TestSQLCredUser FROM LOGIN TestSQLCredUser

EXEC SP_ADDROLEMEMBER [DB_DataReader], TestSQLCredUser

EXEC SP_ADDROLEMEMBER [DB_DataWriter], TestSQLCredUser

EXEC SP_ADDROLEMEMBER [DB_DDLAdmin], TestSQLCredUser

GRANT EXECUTE ON [sp_dts_addlogentry] to TestSQLCredUser

USE [msdb]

IF NOT EXISTS (select 1 from sys.sysusers WHERE [Name] = 'TestSQLCredUser')

CREATE USER TestSQLCredUser FROM LOGIN TestSQLCredUser

EXEC SP_ADDROLEMEMBER [db_dtsadmin], TestSQLCredUser

EXEC SP_ADDROLEMEMBER [db_dtsltduser], TestSQLCredUser

EXEC SP_ADDROLEMEMBER [db_dtsoperator], TestSQLCredUser

EXEC SP_ADDROLEMEMBER [SQLAgentOperatorRole], TestSQLCredUser

EXEC SP_ADDROLEMEMBER [SQLAgentReaderRole], TestSQLCredUser

EXEC SP_ADDROLEMEMBER [SQLAgentUserRole], TestSQLCredUser

--################################################## CREATE Credential

##############################################################################

USE [master]

IF NOT EXISTS (select 1 from sys.credentials WHERE [Name] = @.credential_name)

CREATE CREDENTIAL [TestSQLUserCred] WITH IDENTITY = 'TestSQLCredUser', secret = 't3st'

--################################################## CREATE Proxy

###################################################################################

USE [msdb]

DECLARE @.ProxyTable TABLE (subsystem_id int

,subsystem_name sysname,proxy_id

int,proxy_name sysname)

INSERT INTO @.ProxyTable EXEC

sp_enum_proxy_for_subsystem

--@.proxy_name=@.proxy_name,

@.subsystem_name=@.subsystem_name

select proxy_name from @.ProxyTable WHERE proxy_name = @.proxy_name AND subsystem_name = @.subsystem_name

SELECT @.RowCount = @.@.ROWCOUNT

IF @.RowCount = 0 BEGIN

PRINT 'Creating Proxy'

EXEC SP_ADD_PROXY

@.proxy_name=@.proxy_name,

@.credential_name=@.credential_name

END

DECLARE @.LoginProxyTable TABLE

(proxy_id int,proxy_name sysname,

flags int, [name] sysname, sid varbinary(128),principal_id int)

INSERT INTO @.LoginProxyTable EXEC

sp_enum_login_for_proxy

--@.proxy_name=@.proxy_name, @.name=@.UserName

select proxy_name from @.LoginProxyTable WHERE proxy_name = @.proxy_name AND [name] = @.UserName

SELECT @.RowCount = @.@.ROWCOUNT

IF @.RowCount = 0 BEGIN

PRINT 'Granting Login to Proxy'

EXEC SP_GRANT_LOGIN_TO_PROXY

@.login_name=@.UserName,

@.proxy_name=@.proxy_name

END

EXEC SP_REVOKE_PROXY_FROM_SUBSYSTEM @.proxy_name=@.proxy_name, @.subsystem_name=@.subsystem_name

EXEC SP_GRANT_PROXY_TO_SUBSYSTEM @.proxy_name=@.proxy_name, @.subsystem_name=@.subsystem_name

--###################################################### end script

##################################################################################

PRINT '>>> This execution on server:

['+@.@.SERVERNAME+'] ended at:

['+CONVERT(VARCHAR,CURRENT_TIMESTAMP,113)+'] <<<'

-code end

It will report and error for the

SP_REVOKE_PROXY_FROM_SUBSYSTEM because I haven't found a

way to check this to see if it needs to be executed (anyone have an

idea?)....
HTH|||

I _believe_ you need to enable both SQL Server and Windows authentication mode. The default is Windows Authentication only.

To do this, go into server properties by right clicking on the server node in the object explorer in Management Studio.

Select the Security tab and click on the SQL Server and Windows Authentication mode option.

See the settings here

Kirk Haselden
Author "SQL Server Integration Services"

|||

Hi,

thanks for that script, bu I cant run it. Management Studio says it didnt find database IntegrationServices on sysdatabases :-(. Any idea how to solve this?

@.Kirk: These settings were already set, thanks anyway.

Regards,

Jan Wagner

|||

jwagner20 wrote:

Hi,

thanks for that script, bu I cant run it. Management Studio says it

didnt find database IntegrationServices on sysdatabases :-(. Any idea

how to solve this?

@.Kirk: These settings were already set, thanks anyway.

Regards,

Jan Wagner

:-) Sorry - IntegrationServices database is MY local database. You need

to change it to wherever you plan to have the credential accessing.

Similarly, you should review the rights you may need - the EXAMPLE I

gave was relevant for me....|||

Oh, ok. I thought this database was a systemdatabase, like msdb. So your code does give me credential access to a database, right?

It seems like I did not explain my problem acurately:

What my packagage does is the following: dumping all rows from a database to a MS-Access file that lies on a windows-share. For that windows-share i need the credential login, which is not in my domain, and thats causing my problem...

Regards,

Jan

|||

jwagner20 wrote:

Oh, ok. I thought this database was a

systemdatabase, like msdb. So your code does give me credential access

to a database, right?

It seems like I did not explain my problem acurately:

What my packagage does is the following: dumping all rows from a

database to a MS-Access file that lies on a windows-share. For that

windows-share i need the credential login, which is not in my domain,

and thats causing my problem...

Regards,

Jan

Ok - I think I see now. So your server is in a domain (A), but the

share is outside the domain (in a domain B), and you don't have a trust

relationship, so you are unable to access the target location with a

domain user from your domain (domain A)?

Unfortunately, I can't really help mych other than to say you need to

make sure that the user you use has rights to that share. Does the

share allow everybody full access so that you can access it? I'm really

ignorant around networking, permissions, domain etc. Hopefully someone

else can help out....|||

GethWho wrote:

Ok - I think I see now. So your server is in a domain (A), but the share is outside the domain (in a domain B), and you don't have a trust relationship, so you are unable to access the target location with a domain user from your domain (domain A)?

Yes, thats exactly my problem.

GethWho wrote:

Unfortunately, I can't really help mych other than to say you need to make sure that the user you use has rights to that share. Does the share allow everybody full access so that you can access it? I'm really ignorant around networking, permissions, domain etc. Hopefully someone else can help out....

Thats my problem, I cant use the credential for the user that is allowed to access to the share in my proxy... :-(

Regards,

Jan