May 10, 2011 at 12:55 pm
stormsentinelcammy (5/10/2011)
I have the package level security set to "Dontsavesensitive", before I had it set at default "Encryptsensitivewithuserkey". Yes welsh, I have already crossed lots of bridges in trying to solve this :), but thanks for pointing that out too. At least we can strike out one more possibility off the ledger 🙂
Just as a point you should only use DontSaveSensitive if you are using NT Auth. If you have any SQL Ids & passwords in your package you will need to use EncryptSensitiveWithPassword.
If it makes you feel any better this is a common mistake to make when starting out with SSIS, particularly since EncryptSensitiveWithUserKey is the default.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
May 10, 2011 at 1:09 pm
Hi Ken, Yeah I know its the default and since I've been having these crazy issues I might as well try not saving any sensitive data just in case it was because I was encrypting user sensitive stuff. Even though I have used the do not save sensitive data property I'm still having that issue that I posted the last picture from.
May 10, 2011 at 1:17 pm
Can you log into the server as the Service account for your Agent and try to open the package? On several occasions I've changed things like the encryption level, then it still didn't work, and after a bit I realized I had changed the wrong version of the package, or better yet had forgotten to save.
One of the more annoying (to me) things that SSIS does is if you add a copy of the DTSX to a new solution (to modify it from your desktop say) it makes a new copy of the DTSX and thats the one you are changing.
All of that is the long way of saying double check that the DTSX your job is pointing to is actually the version you changed, and that you actually changed it the way you thought you had.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
May 10, 2011 at 1:29 pm
Hi Ken, how would I logon into the server as the Service agent? I'm not sure how to do that, if you could provide me with another methodology like previously that would be great. I do know what you mean as when you save copy as for the dtsx packages. It is annoying how SSIS does that, but I make sure that when I do "Save Copy of <name of package>.dtsx As..." I save it in either in the MSDB folder or the File System folder. I'm heading out of work now, so I will definitely follow up on anything you suggest for me tomorrow. Thanks so much for your help so far Ken.
May 10, 2011 at 1:45 pm
Remote to the server
Start
All Programs
Microsoft Sql Server 2008
Configuration Tools
Open Sql Server Configuration Manager
SQL Server Services
Look at the row with SQL Server Agent (InstanceName), specifically the Log On As column. Hopefully you are using a service account rather than LocalSystem, NT Auth, etc. Make a note of the service account.
Log out of the server
Log into the server as the service account noted above. Again hopefully you know or have access to the password.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
May 10, 2011 at 1:50 pm
To log on as the service account, note which account is running Agent in the configuration manager and then log into the server with that account. You might need a password change to do that if you don't know the service account name.
May 11, 2011 at 6:20 am
Hi Ken, thanks again for your help, I have changed the Log On As drop down to show Local Service, It was on LocalSystem before. I have attached a picture to show what things look like now that I have changed the "Log On As" to show Local Service. Before I go any further I just want to make sure that it looks like its supposed to. NT Authority\LocalService is now the account that it will "Log On As" when I changed it to Local Service, but I did notice that no where on the server is that account anywhere, either in a group or named as a user, should I add this user to the server? And if so as what kind of user or in what group? The server is set up on a virtual machine, which I don't think should matter when it comes to permissions or usage, just wanted to point that out just in case it does.
May 11, 2011 at 9:09 am
Where is the file located? Is it on the same machine?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 11, 2011 at 9:19 am
Yes the file is located on the server machine and I also have it on my local computer.
May 11, 2011 at 9:27 am
You are using the "NT Authority\LocalService" Account.
I take it that you package trying to access any files on any other machines?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 11, 2011 at 9:34 am
You are right, the package that I am running tries to access files from an FTP, tries to access files from another server and tries to write files to another server. Are you thinking that maybe I may have permission issues because of the other server that I'm trying to access or write files to?
May 11, 2011 at 9:39 am
Generally its a good idea to have an actual service account for SQL Agent just to be sure of your permissions. However I did notice something when I went back and looked at your error. About half way down you start getting errors that say "The file name "\\ipaddress\c$\inetpub\ftproot\igc\.pdf" specified in the connection was not valid." and the same thing for ".....\.xml". You may want to look at your SSIS code and see why these are showing up as your connection strings.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
May 11, 2011 at 9:45 am
Please refer to the following:
http://msdn.microsoft.com/en-us/library/ms191543.aspx
Regards,
Welsh
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 11, 2011 at 9:46 am
The reason you see .pdf and not a file name is because I'm using a foreach loop that creates the file name dynamically using a variable based on what is in the folder with the .pdf or .xml extension. Thats why you'll see the string like this (\\ipaddress\c$\folder\ftproot\igc\.pdf) the variable just fills in the file name so that the sting will look like this when it finishes looping through files with the extension (\\ipaddress\c$\folder\ftproot\igc\hello.pdf)
May 11, 2011 at 9:49 am
Did you notice the following paragraph in the link that I sent you?
You stated that you are using the Local Service Account.
Do not select the Local Service account. The SQL Server Agent service cannot be run under this account. It is not supported. The name of this account is NT AUTHORITY\LocalService, and it accesses network resources as a null session with no credentials. It is available in Microsoft Windows XP and Microsoft Windows Server 2003.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 31 through 45 (of 54 total)
You must be logged in to reply to this topic. Login to reply