April 6, 2009 at 10:48 pm
Have deployed my ssis package to a new server and installed it there with it's associated resource files eg wsdl , xsd etc.
Mofified the configuration file to point to new location of wsdl, xsd etc files.
Then ran the package with "rely on server storage and roles for access control" protection level.
I get an unauthorized error when trying to read from the web service.
I'm not sure if the config file is being read. Is there a way to read the config file values or the settings of the http connection manager in a script file at run time and echo them with a message box. To confirm the settings are right/there ?
Does anyone know if server credentials are wiped when you convert to "rely on server storage and roles for access control"
regards
Erick
April 6, 2009 at 11:36 pm
http://msdn.microsoft.com/en-us/library/dd440760.aspx
I think this video should help you fix the problem you are encountering with your package.
Cheers!
April 7, 2009 at 3:53 am
Thanks for that ..but i get the error even before i include the package in a schedule job
Just clicking on the package in the SSIS engine, which runs it under my credentials (i'm the administrator) and running the first step gives me the same error.
I'm not sure if it is reading the user name and password of the http connection stored within the package.
These values can't be access through the configuration files or even by expressions and variables. I even tried to get the value by interrogaiting the connection object by placing a script file and stopping it in debug. No luck.
If i open the package through an xml editor i can see that the password is encrypted but the username is definately stored in the package.
April 7, 2009 at 9:53 am
I'll suggest you test your http connection manager and call the web service using the web service task
to confirm if your package can downlad the required wsdl file that describes the web service.
Just isolate the web service part of your package and test as described above to be sure your package is
communicating with the web service.
Give this a shot and see how it goes.
If this works fine, then create your configuration file and make sure you include all the required
files during migration to the new environment.
Cheers!
April 7, 2009 at 6:35 pm
Ok a quick update. Thanks for everyones help
Scheduling the package was a bit of a mess.
Firstly according to video I needed a Proxy. (Never heard of one) so a quick read up on what it was.
Secondly I needed Credentials (never heard of this before either) so a quick read up on that as well.
Created both based on a (fairly weak) network account.
I now start getting sp_DTS_ errors like can execute sp_dts_openfolder.
So i gave my weak nt account execute on all of these procedures.
Untill the last error
"Only the owner of DTS Package or a member of the sysadmin role may create new versions of it"
I couldn't find any stored proc that allowed this so i added my weak nt account to sysadmin role.
it worked but i have a really bad feeling about doing that. Why should a weak NT account now by a sysadmin on the server.
it makes no sense.
Anyone know a way around this ?
April 8, 2009 at 1:45 am
Just a quick one, Did you remember to add the fixed server role db_ssisadmin to your package reader role during deployment?
April 8, 2009 at 3:43 am
No i have't yet. I ran into other issues
My package needs to read wsdl files and saves the web services data temporarily to a xml file.
I started getting access denied errors when sql started wanting to write files to the to the C:\Documents and Settings\Server\Local Settings\Temp folder.
So i gave me weak nt account access to that folder alone.
Then i started to get request to access assemblies on the server.
So I added my weak account to the local machine admin account. you'd think that would fix it all but no.
I'm still getting errors.
Altough the package completes and says there are no error in fact the web service component is failing each time.
April 9, 2009 at 1:06 am
do you mean db_dtsadmin role as i can't find a db_ssisadmin role
the db_dtsadmin is a default role
where can i find db_ssisadmin I score a blank on books online...is it sql2005 ?
April 9, 2009 at 9:12 am
Yes you are right, db_ssisadmin is in 2008. if you don't mind, i'll suggest you do a clean deployment of your package and test it before scheduling.
If this works fine, then ensure that the proxy account that will be running the job is a member of the SQLAgent (Operator, Reader, User) roles.
Try this and see how it goes. Cheers!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply