Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a SSIS package that migrates data from a Sybase database to SQL Server. The connection to the source database has been made through an ODBC connection.

If I run the package directly on my machine, it runs successfully, however if I want to run it through a SQL Server job I have created on my local machine (where the destination database is), I'm getting a login failure error:

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Login failed for user 'xxx\user1'.".  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Cannot open database "MySybaseDatabase" requested by the login.

I know that the job runs as 'SQL Server Agent Service Account' that is in fact xxx\user1 and it is different from a user has been set up in ODBC connection to access the source database, but don't know why the odbc connection login does not take the precedance and is used rather than the agent service account?

I read some posts in different sites, they were talking about setting up proxy, etc, but don't know if it is quite relevant to my issue.

The connection to the source database has been set up via ODBC, but as the step was failing, I added the userId and password to it to force SQL uses the userId I want rather than SQL Sever agant user, but it didn't help. enter image description here Do you know how we can fix this login issue?

Thank you.

share|improve this question
    
In your sybase connection manager, what is the connection string property look like? Are you applying a configuration to it? –  billinkc Dec 10 '12 at 2:08
    
Yes, it is done in ODBC connection, and the test connection can be established successfully there, but I know know how force SQL Server job use that credential, e.g: userId = dba not the SQL server Agent user. –  Sky Dec 10 '12 at 23:00

1 Answer 1

up vote 3 down vote accepted

I had a similar problem while importing data from an Excel file using SSIS packages. I found this tutorial and solved the problem.

EDIT: Important steps:
1 - When saving the SSIS package to the file system, select the "Package protection level" to "Do not save sensitive data".

Then, follow these steps:
1 - Open the SSIS package in Visual Studio
2 - Select SSIS -> Package Configurations
3 - On the Package Configurations Organizer click Add
4 - On the window that'll open click Next
5 - Set the configuration type to XML configuration file
6 - On the Configuration file name click Browse, select the folder in which you want to store the configuration file, choose a name to this file and then hit Save
7 - When you're done here, click Next
8 - Choose the right connection manager and click on the + sign to expand it, then expand Properties and check the checkbox correspondent to Password. Then click Next.
9 - Choose a name to your configuration file and click Finish.

The following XML configuration file will be created on the location you specified previously (here I'm using the code from the tutorial as an example):

<?xml version="1.0"?>

<DTSConfiguration>

      <DTSConfigurationHeading>

            <DTSConfigurationFileInfo GeneratedBy="Domain\UserName" GeneratedFromPackageName="Package" GeneratedFromPackageID="{77FB98FB-E1AF-48D9-8A43-9FD6B1790837}" GeneratedDate="22-12-2009 16:12:59"/>

      </DTSConfigurationHeading>

      <Configuration ConfiguredType="Property" Path="\Package.Connections[runeet2k8.sa].Properties[Password]" ValueType="String">

                  <ConfiguredValue></ConfiguredValue>

      </Configuration>

</DTSConfiguration>

The only thing you'll have to do here is edit the XML file and change this:
<ConfiguredValue></ConfiguredValue>
into this:
<ConfiguredValue>YourPasswordGoesHere</ConfiguredValue>

Then save the package.

When creating your SQL job, don't forget to add the configuration file to the job in the tab Configurations.

share|improve this answer
1  
@dezso I've edited the answer in order to contain all the major steps I followed to solve the problem. –  dirtdiver24 Sep 3 '13 at 13:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.