SSIS Sql Server Configuration Logon Error

Question

1.00/5 (1 vote)

See more:

I have been struggling with this on and off for a couple of months and really need to get a solution.
We are using SSIS on SQL Server 2008 R2 via Visual Studio 2008 and have configured our packages using SQL Server configuration. Everything is fine on DEV but when we go to UAT the packages are executed under our UAT account as you would expect.

When the package is run via dtexec, SSIS attempts to load configuration values from the DEV database (because this is how the connection manager is saved in the package) despite the connection to UAT being passed in to dtexec. This causes an error because the UAT account does not have access to the DEV database. This is logged as:

CSS

Started:  09:44:52
Error: 2013-10-17 09:44:53.29
   Code: 0xC0202009
   Source: <packagename>xxxxxx Connection manager "xxxxx<CMName>xx"
   Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E4D  Description: "Login failed
 for user 'xxx<UserName>'.".
End Error

The package then continues to connect to the UAT database, successfully load the config from there and execute normally. However the log is causing support to constantly think that this is the reason for any later non-related errors.

Posted 16-Oct-13 23:16pm

Member 8607591

Updated 17-Oct-13 0:04am

v2

Add a Solution

2 solutions

Solution 1

Hi,

You should use proper deployment strategy either File System Deployment or SQL Server Deployment.

You need to set Protection level of your package appropriately. then specify
Configuration values in .dtsconfig file properly. set password in configuration files. and then you can try.

if you are scheduling job in sql agent then you should use sql proxy account to run this package.

let me know if you still have any confusion.

Regards,
Mubin

Posted 17-Oct-13 0:03am

Mubin M. Shaikh

Comments

Member 8607591 17-Oct-13 6:09am

We are using sql server configuration and deploying the dtsx files to each server as we promote the packages through from dev to prod. Each database had its own ssis-config table

Mubin M. Shaikh 17-Oct-13 6:22am

is user used to execute package has right on accessing this config files in your database?

Member 8607591 17-Oct-13 7:13am

Yes the package is run via autosys which runs under a system account. The dev account only has access to the dev db and the uat account only has accesd to the uat database

Member 8607591 17-Oct-13 7:20am

I think this article covers it but i cannot suppress the error message in the log file
http://technet.microsoft.com/en-us/library/ms141682(v=sql.105).aspx

Understanding How SSIS Package Configurations Are Applied at Run Time When you use the dtexec

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 8607591 · Accepted Answer · 2013-10-17T11:20:00

Hi

I've managed to find an answer by going over some of the SQL Server Configuration examples and best procedures.

By adding a separate xml configuration to the package (it works with an environment variable too) I can set the connection string for the connection manager that is used for the SQL Server configuration.

That way any changes required to the ConnectionString for the configuration are done at the configuration stage as the package is loaded.

This linked gives an example, using an environment variable 31-days-of-ssis-sql-server-configuration