ConnectionManager arbitrary behavior

Question

ConnectionManager arbitrary behavior

georg.schumann

Old Hand

Points: 320
More actions
March 9, 2010 at 8:45 am

#143510

Hi experts,
i am experiencing a strange behavior in a SSIS package of mine.
Setting:
- I am using SSIS 2005 to connect to a SQL Server 2008 DB.
- The Client (where the package runs) and the Server (where the SQL Server 2008 resides) are in different domains.
- I am using SQL Authentication in the ConnectionManager. When I try to connect to the DB with this Account with SSMS2008 it works perfect.
- I am the only one who uses this SQL account.
Now the problem:
When I press "test connection" in the ConnectionManager Dialog I SOMETIMES get the error:
"Test connection failed because of an error in initializing provider. Login failed for user 'user_name' because the account is currently locked out. The system administrator can unlock it"
JUST before I did this I connected via SSMS2008 and it works fine.
I waited a while until the account was unlocked and tried again and it worked with the "Test Connection". Two days later it didn`t....and so on.
Does anybody know about this behavior? Any tipps for hunting the error source down?
Thanks in advance!
Cheers,
Georg

Viewing 8 posts - 1 through 7 (of 7 total)

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

You might check the errorlog and see if there are any failed logins for that user. Something is causing a lockout condition. You might also run a trace to watch for logins/logouts.

CEWII

georg.schumann Old Hand Points: 320 More actions · Answer 2

Hi Elliot!

Thanks for the quick response. In the errorlog there are failed logins which cause the lockout. The strange thing is that with the same package sometimes the "Test Connection" succeeds and sometimes it doesn`t.

Which events do you suggest as most useful for this purpose? All the "Audit Login" & the Audit Logout? Any others?

Thanks in advance!

Georg

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

Login, Logout, and Login Failed. But if you are getting the failures from the log I'm not sure you need to trace it.. Your errorlog should show the IP address that the connection came from.. To help you track down any "wayward" connections..

CEWII

P Jones SSChampion Points: 12361 More actions · Answer 4

Have you tried connecting to the 2008 server and database from Management Studio on the 2005 server?? Get that working first. Cross domain can be a problem as trusts are needed (and even more trust between you and the network guys!)

georg.schumann Old Hand Points: 320 More actions · Answer 5

Hey guys!

Thanks for the replies. I got it working from SSMS2008 to connect to the Server. I didn`t know that trusts are needed for SQL Authentication type?! I am aware that trusts are needed in cross domain enviroment when using Windows authentication. The domain controller is polled if the windowslogin is already logged into the domain.

I monitored the Server Logs, as Elliott suggested, in comparison with my own SSIS Logs and HOPEFULLY pinned down the problem. The SQL Server 2008 is a named instance. There is NO default instance on this server but the default port 1433 is mapped to the 2008 instance. In some packages I just used the Servername without instance. That works sometimes but seems to generate problems. Now I exchanged all ConnectionStrings to use the ServerName\Instance_Name and tests are looking promising.

Thanks for your help!

Georg

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 6

They are no trusts required for SQL Authentication, that is the point of SQL Authentication. If you have a valid username and password you can login. That is about it..

CEWII

georg.schumann Old Hand Points: 320 More actions · Answer 7

Hey everyone,

I finally fixed the problem! There is one aspect of SSIS which I was not aware of (and though did not go in that direction). When opening a package EACH dataFlow is validated (by an SQL Query) with the specified ConnectionManager. We didn`t save the password for the SQL Account in the ConnectionManager. Additionally the SQL Account had set "enforce password policy"(lock out after 5 failed logins). So each time I opened a package with more the 5 dataflows the account gets locked. So the behavior seemed wired for me as for less than 5 DataFlows it worked fine 🙂

Thanks for all your tipps and replies!

Best regards

Georg