Do you mean Run the job as Proxy Account that is probably referencing another domain account. then use Integrated Security=SSPI; in connection string? I tried it, still shows Login for ProxyAccount failed Garry

Hi, all When using a SQL login/password in OLEDB connection string, everything is working as expected. But when replace them with a Domain user ID / password respectively, it shows An OLE DB record is available ... Login failed for the Domain user ID, ... Failed to acquire connection "xxxOleDB". Connection may not be configured correctly or you may not have the right permissions on this connection. Data Source=mySQLInstanceName;Initial Catalog=myDataBase;Provider=SQLNCLI10.1;User Id=myUsername;Password=myPassword; Any idea? Garry

Do you mean Run the job as Proxy Account that is referencing the domain account. then use Integrated Security=SSPI; in connection string? I tried it, still shows Login for ProxyAccount failed Garry

Does the domain user has the required permissions ? Are you able to connect to the SQL server using the same domain user and password ?

Do you mean Run the job as Proxy Account that is referencing the domain account. then use Integrated Security=SSPI; in connection string? I tried it, still shows Login for ProxyAccount failed Garry Sorry, confused. what are you trying to achieve? If your domain user is able to connect as sysadmin, you dont need setup any proxy Account in sqlserver. Just use your normal domain user to connect (using SSPI integrated security in connection string). Once you connected, call system stroed procedure: sp_run_jobIf you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer. Sevengiants.com

Garry, When using integrated security, aka trusted security, aka using a domain account, then as SSISJoost said, skip the credentials and use Integrated Security=SSPI instead. You cannot specify domain credentials on a connection string. The domain user that runs the process, is the user that gets automatically passed through when making the OLE-DB connection. By default when running a SQL Agent job the domain user is inherited from the SQL Agent Service. If you want to change that then your need to use a proxy account for the job to "run as". I'd suggest you try the MS documentation as well to get a clear understanding of the concepts - http://msdn.microsoft.com/en-us/library/ms189064.aspx Now if all of that is in-place, and you indicate it is, then you need to work out why your proxy account is not being accepted. A simple test is to logon to the server using the proxy account and try and connect to the target server using SSMS or a similar query tool. Take SQL Agent and proxy accounts out of the equation, and just check that the user can connect using a simpler tool. http://www.sqlis.com | http://www.konesans.com

I don't believe you can specify a particular username using integrated security. http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/2c889d78-ada8-472f-80d6-0035e3e7a86c

Sorry, my mind is mixed up as well. Basically, I have 2 environments: 1. one environment in client's boxes, where I have very limitted permission. I do the SSIS 2008 development on Machine A, and the SQL database engine and SSIS service are on Machine B. I can log into the SQL Engine and SSIS through SSMS, using a domain user account with ssisltduser, sqlagentuserrol. I was given a network share UNC (\\machinename\sharefoldername), and a proxy account to for packages to Run AS. No SQL login, no local path. 2. Feeling dificult to troubleshoot, I created another environment in my local machine, where I have everything. What I want to do is, to try or confirm whether I can set up a SQL job: 1. use the domain user account, or the proxy account 2. use the UNC as a store for my SSIS packages 3. I can import my SSIS packages into SQL MSDB or File System. But I guess I still have to use the UNC for the checkpoint file. Maybe that is why my words seems inconsistent sometimes.Garry

Or you use integrated security and give your domain user the correct rights within SQL server: Data Source=yourserver;Initial Catalog=yourdatabase;Provider=SQLNCLI.1;Integrated Security=SSPI; Or you use a database user: Data Source=yourserver;Initial Catalog=yourdatabase;Provider=SQLNCLI.1;User Id=myUsername;Password=myPassword; You can't mix them. See this site for more examples: http://www.connectionstrings.com/ Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

yes, my domain user can connect to the SQL server with sysadmin permission So it has been confirmed, there is no way to use domain user id in the connection string, especially in unatendded SQL job? Thanks Garry

Set your SQL Agent job to use a Proxy Account that is referencing the domain account as described here: http://www.databasejournal.com/features/mssql/article.php/3789881/Proxy-Accounts-in-SQL-Server.htmArthur My Blog

yes, my domain user can connect to the SQL server with sysadmin permission So it has been confirmed, there is no way to use domain user id in the connection string, especially in unatendded SQL job? Thanks Garry Yes, you can. You can just use integrated security in your app, and ask your domain user to log on as his credential to your server and run that app. Or you can use RUN AS to use different domain user.If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer. Sevengiants.com