July 10, 2015 at 9:49 am
I've been pulling my hair out for days on this. A developer has created an SSIS package that has a SQL login called PatsETLService for one of the connection managers. The package is working fine in dev because he created the package and deployed it to dev. Now I can't deploy it to production because I don't own the package and the SQL login password isn't saved inside the package. There is also a password on the package itself and the package was saved with EcryptSensitiveWithPassword. I imported the package into production and entered the encryption password. This is in SQL 2008.
Things I've tried:
-Verified that the SQL login (PatsETLService) password is correct
-Changed the owner of the package to the account running the job, Domain\ServiceAcct.
-Created password in data source tab in the SQL job and didn't use config file. Didn't work.
-Created new job that used config file and manually entered the SQL user and password. Didn't work.
Other than giving the developer temporary permissions to deploy his package to production I am out of ideas. It's not a solution I want to do and I'd like to know how to do this.
Any help would be greatly appreciated!
Elizabeth
July 10, 2015 at 9:51 am
One more thing I tried - in the Command line tab in the SQL Agent job, I edited the command line to include the SQL login account and password. Didn't work.
Elizabeth.Block (7/10/2015)
I've been pulling my hair out for days on this. A developer has created an SSIS package that has a SQL login called PatsETLService for one of the connection managers. The package is working fine in dev because he created the package and deployed it to dev. Now I can't deploy it to production because I don't own the package and the SQL login password isn't saved inside the package. There is also a password on the package itself and the package was saved with EcryptSensitiveWithPassword. I imported the package into production and entered the encryption password. This is in SQL 2008.Things I've tried:
-Verified that the SQL login (PatsETLService) password is correct
-Changed the owner of the package to the account running the job, Domain\ServiceAcct.
-Created password in data source tab in the SQL job and didn't use config file. Didn't work.
-Created new job that used config file and manually entered the SQL user and password. Didn't work.
Other than giving the developer temporary permissions to deploy his package to production I am out of ideas. It's not a solution I want to do and I'd like to know how to do this.
Any help would be greatly appreciated!
Elizabeth
July 11, 2015 at 10:14 am
Hi
First you have enter development of the package again
Then choose DontSaveSensitive
Configure a new configuration file for the package and edit it to save login and password
Edit again the configuration file to enter manually the password of the connection
Then schedule the package
be careful because the path of the config file is stored within the package itselft
if you want to edit it you have to xml edit the package and serch for the .dtsconfig the change the path to be convenient
hope this will help
:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:
July 13, 2015 at 10:40 am
Mohamed IDTTALBE (7/11/2015)
HiFirst you have enter development of the package again
Then choose DontSaveSensitive
Configure a new configuration file for the package and edit it to save login and password
Edit again the configuration file to enter manually the password of the connection
Then schedule the package
be careful because the path of the config file is stored within the package itselft
if you want to edit it you have to xml edit the package and serch for the .dtsconfig the change the path to be convenient
hope this will help
Mohamed,
I must be missing something. I edited the package in BIDS and chose DontSaveSensitive and then edited the package to change the path to the config file. Then I reimported it into the server and chose Don'tSaveSensitive.
Then I edited the original config file with the correct path to include the user name and password.
Then I created a new job step to run the package. I entered the config file in the Configurations file. It still fails. I've tried it two ways, one entering nothing for Data Sources and Command line tab and one entering the username and password in those 2 tabs.
What am I missing?
Thanks for your help,
Elizabeth
July 13, 2015 at 11:26 am
In fact I always use the command line dtexec instead of scheduling in sql server
First do u use a 32bits computer to make your development and a 64bits in production?
If yes then be careful because some data driver works only in 32bits mode
Second did you export only the login and password properties in your configuration file?
If you export other properties you can expose in misconfiguration. For example the connection path.
Try using the dtexec program to see if it works fine on your production server.
Can you post the exact error message please?
:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:
July 13, 2015 at 11:37 am
Mohamed IDTTALBE (7/13/2015)
In fact I always use the command line dtexec instead of scheduling in sql serverFirst do u use a 32bits computer to make your development and a 64bits in production?
If yes then be careful because some data driver works only in 32bits mode
Second did you export only the login and password properties in your configuration file?
If you export other properties you can expose in misconfiguration. For example the connection path.
Try using the dtexec program to see if it works fine on your production server.
Can you post the exact error message please?
All of our database servers are 64bit.
I'll have to check the config file for what I exported. I only exported the connection properties, which include some text files for SQL queries. I'll post what I find in a minute.
Error message:
Message
Executed as user: Domain\SSISSrvcacct. Microsoft (R) SQL Server Execute Package Utility Version 10.0.4000.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:29:39 AM Error: 2015-07-13 10:29:39.85 Code: 0xC0202009 Source: Crook Connection manager "Crook" 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 'PatsEtlService'.". End Error Error: 2015-07-13 10:29:39.85 Code: 0xC020801C Source: Query 1 Data Flow Task OLE DB Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Crook" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2015-07-13 10:29:39.85 Code: 0xC0047017 Source: Query 1 Data Flow Task SSIS.Pipeline Description: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C. End Error Error: 2015-07-13 10:29:39.85 Code: 0xC004700C Source: Query 1 Data Flow Task SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2015-07-13 10:29:39.85 Code: 0xC0024107 Source: Query 1 Data Flow Task Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:29:39 AM Finished: 10:29:39 AM Elapsed: 0.655 seconds. The package execution failed. The step failed.
July 13, 2015 at 11:52 am
I've included the relevant part of the config file that deals with the Crook connection manager. I've replaced sensitive values with descriptive names.
<?xml version="1.0"?>
<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="Domain\developer" GeneratedFromPackageName="Crook" GeneratedFromPackageID="{907439AD-62EB-40D6-BD21-CBEE855A21B1}" GeneratedDate="7/13/2015 10:21:37 AM" />
</DTSConfigurationHeading>
<Configuration ConfiguredType="Property" Path="\Package.Connections[Crook].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=IPADDRESS\ServerName\InstanceName,Port;User ID=PatsEtlService;Password=PatsPassword;Initial Catalog=CrookMain;Provider=SQLNCLI10.1;Persist Security Info=True;Auto Translate=False;</ConfiguredValue>
</Configuration>
July 13, 2015 at 12:20 pm
Try this
Leave the connectionstring inside the package without q configuration
Just export the login the password
I think there is a bug with exporting the connection string
:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:
July 13, 2015 at 12:35 pm
Good idea, but same results. I overwrote the config file and only exported the name and the password of the SQL login. I think I'm going to have to look for an alternate method. Very frustrating to spend days on this.
July 13, 2015 at 12:52 pm
OK and you tried forcing it in 32bits?
I think there a is a check box option in the scheduler.
Also when you are in the scheduler leave all the parameter variable blank
As they will take the values from the configuration file
:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:
July 13, 2015 at 12:55 pm
I'm confused. Why would I want to force it to use 32bits?
July 13, 2015 at 1:35 pm
It's to be sure it is not a 32bits/64bits issue.
I don't know what exactly is on your package
Maybe you use a driver which is only working on 32bits mode
:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:
July 13, 2015 at 2:23 pm
Since I'm running this from a SQL job, I think that SQL will automatically run it in 64bit mode. When we install SQL we always choose 64bits so I think it will default to 64.
From technet: https://technet.microsoft.com/en-us/library/ms141766%28v=sql.105%29.aspx
If you use SQL Server Agent to run the utility, SQL Server Agent automatically uses the 64-bit version of the utility. SQL Server Agent uses the registry, not the PATH environment variable, to locate the correct executable for the utility.
July 13, 2015 at 2:35 pm
Then you have to specify the full path of the dtexec located on the programfilesx86
:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:
July 13, 2015 at 2:52 pm
Ok, found more info. The developer did create the package in 32bit mode, which I'm having him investigate to tell me why he did that. But I just ran the command line values from the 32bit dtexec and still got the same password failure. I'm going to cry uncle and try putting the connection string in a variable. It appears there is either a bug or a "feature" that is getting in my way here. Thanks for your input! I really appreciate it.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply