SQL Agent will not allow SQL login connections

Question

SQL Agent will not allow SQL login connections

gpestka

SSC Rookie

Points: 41
More actions
January 10, 2006 at 7:36 am

#167729

(SQL 2005) When I include a database connection in a SSIS package that utilizes a SQL login, and run the package via a SQL Agent job, the SQL Agent job will fail. If the connection in the package utilizes Windows authentication to the same database, then the SQL Agent job will not fail. The SQL login is good, and the SSIS package will run fine from the Business Intelligence Studio, as well as from the Server Management Studio when run seperate from the SQL Agent job. Below is an excerpt from the package logging that shows the error encountered when run from the SQL Agaent job. My test user is "mso" for this test setup.
5:39:19 PM,1/9/2006 5:39:19 PM,-1073573396,0x,Failed to acquire connection "HEIWEBAZ014.MSO_Mirror.mso". Connection may not be configured correctly or you may not have the right permissions on this connection.
Can anybody advise me on this? Our environment requires that I use SQL logins for these database connections.
Thank you,
Gary P.

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 1

SQL logins verse nt right? So try the sa in the connection manager to see if it is a permission thing between the agent and your login.

gpestka SSC Rookie Points: 41 More actions · Answer 2

JKSQL

Thanks for your reply. This is SQL logins vs NT. Actually, I had previously tried the SA login, with the exact same result. This issue is not rights. Any other thoughts?

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 3

sorry if sa is the owner and still can not connect it must have to do with the integration server. Did you upload it through the manifest file?

gpestka SSC Rookie Points: 41 More actions · Answer 4

I am sorry to say that I don't know what file you are referring to as the manifest file. If you are referring to the source files for the SQL 2005 install, this is not a beta install and the instance was installed from a CD recieved from MS. As far as the SSIS package goes, I created it in Business Intelligence Studio and in SQL Management Studio I imported the package. I can run the imported package by itself, and it authenticates to the database just fine, using the SQL login. When I create an Agent scheduled job to run the package, the job will fail with the error noted in my original posting.

Any other thoughts? I am game for anything at this point.

cortec SSC Eights! Points: 962 More actions · Answer 5

I went through something similar to this with SQLServer 2000 Ent. 64bit. It seemed that anything I tried the default action for SQL Agent is to use the NT authentication therefore it could not recognize a domain account such as 'sa'. This might be something to investigate.

gpestka SSC Rookie Points: 41 More actions · Answer 6

I tried using the 'sa' SQL login, and the agent job still fails. All of the SQL logins I used work fine when the package is executed on it's own, and not from the agent job. The agent job will only work if Windows authentication is selected within the SSIS package connecction. Any other thoughts?

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 7

So when you import the dts into BI-2k5 you have not uploaded the file to the servers integration server. There is a process to get that file on the server so the Agent can run it. First thing you need to do is right click on the project and select properties. Then set deployment to true. After that right click on the project again and select build. Under the deployment folder you will now see a manifest file which will get you to upload the package to the integration server where the agent will see it and have permission to run it. Just tell me if you need more info

gpestka SSC Rookie Points: 41 More actions · Answer 8

The SSIS package that I am working with was created within BI-2k5, and not imported from a DTS package. As far as the project properties go, you mention setting "deployment" to "true". Are you referring to the property called "CreateDeploymentUtility" that is located in "Deploment Utility" in the tree? Is the manifest file you refer to actually a package file with a "dtsx" extension?

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 9

Are you referring to the property called "CreateDeploymentUtility" that is located in "Deploment Utility" in the tree?

--Yes

Is the manifest file you refer to actually a package file with a "dtsx" extension?

--no

After you set the deployment to true and build the project you will see a deployment folder under the bin folder in your project. extension - .SSISDeploymentManifest

Double click on that and it will ask you where to upload the file on the server. Then once that is done look for it on the integration server. It should be there under file system. Then you can create a job for it. just tell me if you need more.

gpestka SSC Rookie Points: 41 More actions · Answer 10

I set CreateDeploymentUtility to 'true', and selected Build as you suggested. I do not have a Deployment folder appearing. I even closed and reopened BI with no Deployment folder appearing. Below is a list of all the folders I do have in this project.

Data Sources

Data Source Views

SSIS Packages

Micellaneous

Thank you for sticking with me on this...any suggestions?

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 11

I am sorry I have issues writing what I am thinking sometimes. It is in windows explorer. example below.

C:\SQL2005\SSIS\GermanShepherd\BloodHound\bin\Deployment\*

So once you build it puts it out there. If you want you can call me 636.443.3763

Chris McGuigan SSC Veteran Points: 210 More actions · Answer 12

I'm having the same problems but I have uploaded the SSIS package via the manifest file.

The package runs from within BIDS aswell as right clicking and running from the integration server itself. It fails only when run from a job.

I've tried using both 'sa' and an NT admin account, but it always seems to be trying to use SQLAdmin which is, I assume, because the step will only run as SQLAgentServiceAccount.

I've added the the SQLAdmin account to MsDtsServer component in DCOM Config in Component Services snap-in. But with no affect.

I'm missing some security setup but I can't work out where?

Thanks in advance for any help!!

guillaume-299378 Newbie Points: 1 More actions · Answer 13

Hello chris

Actually, I have exactly the same problem than you. I created a package thanks to BIDS and like you, it runs from BIDS.

I can also double click on the .dtsx file and a configuration window appears with the same tabs than when you are configuring the step inside the job.

By this way, i manage to make the package run on the condition that i don't tick the connection manager inside the connection managers tab otherwise it doesn't work.

Do you have the same behavior with your package ??

This is very od because when i put my package inside a job, even if the connection managers are not selected, it doesn't work.

Tell me if you manage to find a solution !

Regards.

Tony White-278402 Newbie Points: 4 More actions · Answer 14

Hiya Chris,

I have the exact same problem here at Zxxxx in South Cerney. ~The package runs when I right click and choose execute in management studio, runs OK in BIDS but the job fails. Looking at uk-bids, the application log is telling me "login failed for user reports". I know the name and password are correct (after all, it runs when I execute it).

Did you ever get a solution to this?

Tony W