I had a lot of problems with this too, the way I was able to resolve it was to configure the SQL Agent to run as a domain service account, and then log into the server under the domain service account and create my SSIS package. Save the package to disk and then import it into SSIS, use the SQL Agent to run a job that points to the SSIS package and it then succeeded. In my case it was a security issue when the SQL Agent was running under a different account than the one I had created the package under. Once I used the same account to do everything I was able to get it to work.
|||
Hey Andy
Thanks for the reply, it seems that the issue is that I'm using a 64 bit OS and 32 bit odbc driver and when sql agent runs it uses the 64bit program files as opposed to the 32 bit program files which means that it couldn't find my odbc dsn. So to get around this had to create a batch file which calls the 'x86' program files.
Have also looked at getting a 64 bit odbc for my postgres database but seems that SSIS does point at 32 bit odbc connectiosn so don't see how to include the 64 bit odbc in SSIS.
Ugly and messy!
|||Hi there,
Ahhhh yes, I tangled with this one too. What's happening is the SQL Management Studio, the Business Intelligence Studio, and SSIS are compiled as a 32 bit applications, the only 64 bit tool I have been able to find is the x64 DTS Wizard and it needs to be run from a command line or by double clicking it in Explorer. By using this you can connect to 64 bit ODBC drivers but you can't run any job as an SSIS package because there is no 64 bit SSIS. I was using an Oracle provider and found out it has a SQL Net issue with any path names like (x86), so I had to get a patch for that. So although you can install and run SQL 2005 on the x64 platform, 99% of the functionality is 32 bit.
Crazy stuff huh?
|||
I have an asp.net web application and a web service (both of them are created in VS 2005 - asp.net 2.0). They are located on the same web server. In both web.config files, I have set <authentication mode="Windows"/> and <identity impersonate="true"/>. Also, configured the IIS settings to use Integrated Windows Authentication and unchecked the Anonymous access (for both). The web service is called from the web app, so I have to pass credentials for authentication to the web service. The web service loads and executes a SSIS package. The package and all the other sql objects are located in the sql server 2005 (windows server 2003 - the same server as the web server).
When run the web service from develop environment (vs. 2005), I get whatever I expected. When call it from web application, however, the package failed (no error message).
From SSIS package log file, found that the user credentials (domain and username) were correctly passed from web service to sql server 2005 at the first event - packageStart, but the credentials (or operator) changed from domainABC\user123 to NT AUTHORITY\NETWORK SERVICE after packageStart. Then, it complains … either the user, domainABC\serverName$, does not have access to the database, or the database does not exist.
Please help and thank you all!
|||Jenny,
It sounds like the credentials of your web application pool are being passed to SQL Server, the default account for the application pool is NT AUTHORITY\NETWORK SERVICE. Have you checked that?
|||
I had the same issue: A package using a 32-bit ODBC driver, it ran fine in my laptop but not in my 64-bit server. The work arround was to schedule it through an SQL Agent that used a command line to invoked the 32-bit version of dtsexec. The bottom line: the package runs in a 32-bit mode instead of 64-bit mode
Rafael
|||Hi Andy, thanks for the response.
I have worked on something else these two days. I just checked the web application pool and both my web app and web service are pointed to the DefaultAppPool and its property setting for Identity as Predefined - Network Service. Should I create a new web application pool for both my web app and web service to use? I do need impersonation. How do I configure the new web application pool? Thanks!!!
|||Here, are some more info.
In the SSIS package, there are three connection managers –
· A:Microsoft OLE DB Provider for Analysis Services 9.0 à connectionType=OleDbConnection
· B: .Net Providers \ SqlClient Data Provider à connection type=SqlConnection
· C:Native OLE DB \ Microsoft OLE DB Provider for SQL Server à connectionType=OLEDB
After ran the web application and check the sql database, I can tell that the package was reached and when through the first two steps which relate to the connection manager B – ADO.Net connection. The remaining steps failed which are related to the connection managers A & C. I think the credentials are passed ok but some setting related to the Analysis services are not correct. Any clues?
|||Jenny,
There are a few different ways to go about this so why don't we try the easiest way first. According to the error message you are receiving, the domain\server$ does not have access to the database. So what's happening here is because your web application pool is running with the credentials of network service, SQL Server is viewing the account as ServerName$. So what you need to do is go into SQL Server>Security and add a new user with access permissions to the database. When you add the new user, type in the domain name and the server name with a $ at the end of it, for example: mydomain\myserver$
Make sure under the user mappings you check the box of the database that the user needs access to and also check the datareader box. This will give your web server access to read the database. If you need write permissions you can go back and check the datawriter box.
Typically you should run your web application under a domain service account, provide the domain service account access to the database, and use Windows authentication, something like this for your connection string: "server=MySQLServer; Integrated Security=SSPI; database=MyDatabase"
There are probably specific reasons why you want to use Impersonate, so give the mydomain\myserver$ example a try and see if you can get it to work.
|||Hi Andy,
FYI: The problem was solved. What I did are list as below:
A. We created a new web app pool and pointed the both web application and web service to this web app pool (which was configured not use default identity - Predefined: Network Service but use Configurable - created a new use name).
B. Under SQL server , added this new user and assign the certain rights to it.
C. In the web application and web service, set impersonate=false (instead of true)
D. In the SSIS package, we also have a flat file destination connection which is to write the output to .txt file. I give the read & write rights to this new use. then, it works.
Thank you very much for your kind help!!!
Jenny
|||Had a somewhat different problem... BIDS is running 32 bit and connecting to 32-bit ODBC drivers (Paradox DB in this case), but when executed, the server keeps looking for 64-bit drivers. While I wish it would look for ALL possible drivers and automagically drop down from 64 to 32 if necessary, setting Run64BitRuntime to False fixed it for now (which is too bad, IBM has 64 bit drivers for the AS400). Haven't scheduled the package yet, though.|||Hi,Andy_T wrote:
Hi there,
Ahhhh yes, I tangled with this one too. What's happening is the SQL Management Studio, the Business Intelligence Studio, and SSIS are compiled as a 32 bit applications, the only 64 bit tool I have been able to find is the x64 DTS Wizard and it needs to be run from a command line or by double clicking it in Explorer. By using this you can connect to 64 bit ODBC drivers but you can't run any job as an SSIS package because there is no 64 bit SSIS. I was using an Oracle provider and found out it has a SQL Net issue with any path names like (x86), so I had to get a patch for that. So although you can install and run SQL 2005 on the x64 platform, 99% of the functionality is 32 bit.
Crazy stuff huh?
i'm having the same problem (trying to execute a package located on server A and a web service on server B), but i'm using IIS 5.1, so i think i don't have the web application pool feature.
I also have the package's Package source configured as SSIS Package Store so my path refers to "File System" source type.
from a custom application, i call the web service and i returns "failure" when trying to execute the package (this one is retrieved successfully, started but not executed).
Is there any way to solve this problem on a similar way like Jenny_99?
regards.
rodri|||
I had a similar issue and this is the workaround I found.
We installed SQL2005 with SSIS on a 64-bit windows 2003 server. An IBM ISeries for windows (V5R3M with the latest Service Pack) was used for connecting to AS400. There are actually two versions of ODBC drivers on the 64-bit windows (odbcad32.exe). One is located at Windows\System32. Another is at Windows\SysWOW64. Originally I thought the one in System 32 is 32-bit version, and the other one is 64-bit version. But the driver version number under SysWOW64 is actually consistent with the version number in our 32-bit windows 2003 server. So I am not so sure any more. Anyway, what I found out is that DSN under SysWOW64 is actually used when the package is running in BIDS, or SSIS on the server. The one under System32 is used when the package is scheduled as a job. So I created the same DSN on both drivers to cover all bases since I don't know how to force the SQL agent to use a specific driver.
