Problems with SQL Server Agent

Question

Problems with SQL Server Agent

Viewing 10 posts - 46 through 54 (of 54 total)

You must be logged in to reply to this topic. Login to reply

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 1

To simplify your error message I would change it so that you start out with a valid connection. That way when it does its verification at the beginning you don't get an error. Also just to check do you have any passwords stored in your package?

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]

stormsentinelcammy SSCrazy Points: 2493 More actions · Answer 2

Thanks for the help Ken and welsh, I changed the "Log On As" back to local system and am Logging in as ".\Administrator" for the SQL Server Agent. I ran the job and its still failing. This issue is becoming the never ending story 🙁

stormsentinelcammy SSCrazy Points: 2493 More actions · Answer 3

Thanks Ken, I will make sure that it checks for a valid connection first and no I do not have any password inside the package. The only thing that does have a password is the connection to the FTP site, but I don't think that would be an issue...can it? I don't think it can be an issue since I have to enter a password to establish the connection with the FTP site

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

The Administrative Account that you are using is not a Domain Admin it is a Local Admin.

What Accounts do you use for the SQL Server Agent on other SQL Server machines in your shop?

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/

stormsentinelcammy SSCrazy Points: 2493 More actions · Answer 5

To be honest welsh, I am the first person to actually use SQL Server Agent jobs in my shop. I come from other shops where I could add a job and schedule them without a problem because that was done for me because of the user that the job would always run under. Here I'm basically setting up the service and jobs from scratch. That's why I'm having so much difficulty with this because I've never done it before. Thus I do not know what accounts should be used for the SQL Server Agent in this shop, I wouldn't even know how or where I could find out what account would be appropriate or right to do this. Can you provide me with some more guidance as to do this?

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

You need an Account that has the minimum permissions to read, write to access whatever resources that you need.

It is not recommended using a Domain Account for it grants excessive permissions. If you were to change it to a Domain Admin your package would work.

Unfortunately I do not know your environment but I was wondering who created your Windows Account?

As a Starter you could have someone create an account that has the same permissions that you do minus any permissions that the service account does not need access to, i,e your personal shares, etc.

http://msdn.microsoft.com/en-us/library/ms178142.aspx

http://social.msdn.microsoft.com/forums/en-US/sqlsetupandupgrade/thread/9975ab9e-072e-46c6-b11d-b3b9609373e8/

Regards,

Welsh

MCDBA, MCITP, MCSE

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/

stormsentinelcammy SSCrazy Points: 2493 More actions · Answer 7

Since I'm an admin on the Sever box, I can just pull a user out of the directory and give them rights and then use them as the account that I will run it off for testing. I will do what I just said and get back to you in a few with my results.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

You need to be more than a Local Admin. You need to be a Domain Admin or a Local Admin on each Server that you are accessing resources within your Package.

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/

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 9

A password for an FTP account still counts. You need to change your security level to EncryptSensativeWithPassword. When you go to type in the password hit the elipsys (...) button don't just type it in the field. You will then need to add the password for the package to your job. Thats probably the decryption error we were seeing previously.

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]

stormsentinelcammy SSCrazy Points: 2493 More actions · Answer 10

Thanks for your help Welsh, I will note how Welsh, Steve and Ken helped me finally fix this. Just like Welsh suggested with this link (http://msdn.microsoft.com/en-us/library/ms178142.aspx)

Step 1: Log into the Server that the job runs on (mine was named GAMBIT)

Step 2: Click the Start button

Step 3: Click Control Panel

Step 4: Click Administrative Tools

Step 5: Double Click "Local Security Policy"

Step 6: Go to "Local Policies"

Step 7: Go to "User Rights Assignment"

Step 8: Right click on "Log on as a service" and select "Properties"

Step 9: Check to see if your username is in the list (mine was Core\<Username>)

Step 10: If your username is not there you have to add it to the list (Follow steps 11-14)

Step 11: Click on "Add User or Group"

Step 12: Click on "Advanced"

Step 13: Type your first name in the "Stars with:" box and click "Find Now"

Step 14: Double click the row that has your name in it, then hit "OK" and then "OK" again to exit the Log on as service window

Step 15: Repeat this same process for your client computer (The computer you use to remote log into the server)