April 19, 2007 at 2:35 am
Hi All
I have am faced with an interesting one. Here is the breakdown.
I have a job that executes every morning at 8:00
The Scenario
In the job there are different steps ranging from SSIS packages to t-SQL statements
All SSIS packages are configures to use Windows authentication/integrated security. The T-SQL statements use the SQL server agent user.
The user that executes the T-SQL statements and the SSIS packages is the same user and this user has been given every security permission I could find within SQL 2005 (I might not have found all the permissions)
The symptoms
Firstly on only one of the SSIS packages I get an error message. This is step 5 within the job. If I log onto the server using terminal services and connect to the server using the windows credential, the same credentials that start the SQL server and all relevant services and re-execute from step 5 the package executes without any hassles.
The second issue I have is on step 13 that is a T-SQL statement that connected to a DB and executed a proc. This has been resolved by not connecting to the DB in question but connecting to master DB instead and changing the DB programmatically using the "use DB_Name" command in the statement plane then executing the proc.
The error
This is the error that SQL server has in the server logs
Error: 18456, Severity: 14, State: 16.
Login failed for user 'domain\username'. [CLIENT: xxx.xxx.xxx.xxx]
The error within the job engine history is
For the SSIS package
Executed as user: domain\username. The package execution failed. The step failed.
For the T-SQL statement executing the proc
Unable to connect to SQL Server 'servername'. The step failed.
If you have a look at the time the errors occurred in the Job history and view the SQL server logs the same message is shown.
This "small" problem has given nightmares for the last 3 or so months. We have checked network connectivity to active directory and found no issue what I do know is that state 16 is when the user does not have access to the database.
The user is a member of the administrators group and a user on the server with every role assigned to it with access to every database and also DB owner on all. What I must mention is that the DB's were not created new and was a restore from another server.
Any help would be greatly appreciated.
Jacques
April 19, 2007 at 4:23 am
Jacques,
I've seen similar issues in the past and most of the time the reason for the failed logins is that either in the datasource or hte connection string the process tries to connect directly to a database which does not exists.
If the login just tries to connect to the server without specifying a database name, it will be directed to it's default database. If you specify the name of an invalid database (mispelled DBname) the same login will receive error 18456 Login failed. A similar thing can happen if the you do not specify a datbase during connection, but the default database for the login has been removed. In this case you receive error 4064, "cannot open user default database".
So I suggest doublecheck your datasource definitions and connection strings for any typos or extra spaces.
Good luck
Markus
[font="Verdana"]Markus Bohse[/font]
April 19, 2007 at 4:29 am
Hi Markus
I assumed the same and did double check the datasource connections and redeployed the package. But with no success. I would also assume that if the datasource was incorrect when you execute the step for a second time the same error would appear.
Thanks
Jacques
April 19, 2007 at 7:55 am
Jacques,
With reference to your statement about the error messages being the same in both Job history and SQL Server logs, its the default nature of SQL Server to have similar messages. You would be able to get a clear picture of what the error is if you add a output file under the advanced section of your job step. When the step or the entire job is run, information about what was executed or what errored out is logged into this output file. You will be able to investigate and find a solution using this file. Also, please check the Append output to existing file checkbox.
If the job happens to error out, do post the error message from the output file and may be we can find a solution.
April 20, 2007 at 5:14 am
Hi All
I tried to isolate the error by scheduling the package in a job on its own. This job runns every 10 minutes it has been going for 2 days with "no errors".
With reference to the output file: there is no output file option when executing a SSIS package. I am in the process of adding a output file to all the t-sql statement steps in the hope that I will trap the issue within one of them.
Could it be that because of a previous step connecting to the database that the connection is "locked" and another connection is refused? Hence the reason that the package executes without any problem on its own. What also seemed to have worked is I removed the SSIS package step from the job and added it again. Since this was done yesterday it has not failed today in the warehouse load. The weekend will hopefully give us more to work with or nothing if everything executes as it should.
Jacques
April 23, 2007 at 6:17 am
Hi All
An update. This update makes even less sence than previously thought.
The job failed again on the same step on the weekend. I added a retry to the job to retry once more after a minute. It seemed to have worked.
I am stunned as if it was a security issue as reported in the SQL error log the same error would have occurred. I am unsetteled by this as the symptoms are being treated but there is still a root cause that I am unable to find. Has anybody else encountered this before?
Thanks
Jacques
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply