March 9, 2009 at 4:15 pm
I am a little new to SSIS but have already managed to create several packages and deploy them successfully to my Production server.
However, i have only been able to successfully run my SQL OLEDB Source connection managers under Windows Authentication.
For some reason the permissions fail when i use the SQL Authentication, even though i know i am using the correct Login and Password for my Database Owner.
The way we run our packages is as follows:
- Package deployed to Server (no special configuration settings so far)
- Created Credential and Proxy user for the DB and MSDB
- Created SQL Agent Job to run the SSIS package under the proxy
- Created a Stored Proc to trigger the Agent Job
- Created a web page to run the command Stored Proc
Is there something i am missing with the package configuration for authenticating my OLEDB connections using SQL as opposed to Windows?
March 10, 2009 at 4:51 am
I suggest to use configuration. Configure the connection string of OLEDB server and place the below string for connection string.
Data Source=SERVERNAME\INSTANCENAME;User ID=UserID;Pwd=XXX;Initial Catalog=DATABASENAME;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False;
Hope this helps.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply