Post reply

strage error: 18456, Severity: 14, State: 16. when executing SSIS package

  • April 23, 2007 at 6:27 am

    #116564

    Hi All

    I have am faced with an interesting one. Here is teh breakdown.

    I have a job that executes every morning at 8:00

    The Scenario

    In the job there are different staps 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 hassels.

    The second issue I have was on step 13 that was an 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 programmaticaly 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.

    Subsequently I scheduled the package that fails within its own job to run every 10 minutes to try and catch the error and resolve it. The job has run for 2 days without any errors.

    Another anomaly that has me stunned: I enabled a re-try on the particular step to re-try once after a minute. This has worked and the job now executes still with the first failure but the re-try works. I have never seen something like this not even in my consulting days.

    I have a couple of concerns regarding the above issues. The first is that if it was a security issue the re-try would have also failed. The second is that only the symptoms of the problem is treated but the original problen remains.

    This "small" problem has given nightmares for the last 3 or so months. We have checked network conectivity 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.

    has anybody seen this before?

    Any help would be muchly appreciated.

    Jacques

  • January 17, 2008 at 7:46 am

    #769226

    Read this article and see if it helps. (copy andpaste)

    http://www.corkchop.com/journal/2007/3/28/error-18456-severity-14-state-16.html

  • March 17, 2009 at 11:12 pm

    #961583

    I am having the same problem ....

    I connect via ODBC to SQL Server 2005 express from a Delphi application. It updates the database running a whole bunch of DDL statements. Directly after that is done a dotNet c# application is stated and runs similar DDL statements. However it runs straight into a problem. “Login failed for user 'username'.”

    Both applications use the same login details, and I am 100% that it is using the same connection details. Both obviously run on the same machine, under the same windows user. The Delphi application actually starts the dotNet via shell execute.

    I have read various articles that talk about adding in a delay between the runs or doing retries. But this problem I have is not consistent across all 2000 clients. It only happens at some of them. Also if I run the application manually it runs fine.

    In the SQL Server log I found this “Error: 18456, Severity: 14, State: 16.”.

    Does anyone know what state 16 refers to?

Viewing 3 posts - 1 through 2 (of 2 total)

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