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
