May 16, 2009 at 2:47 pm
I am trying to do a very simple, common task but am failing miserably.
I have 2 machines with SSIS on one and SQL Server 2005 express on the other (mixed mode authentication, default instance). Since SSIS can't connect natively to SQL 2005 express I set up a script object which uses ODBC to connect to the DB to load some data in. This all works well when its all locally.
I then set up an ODBC connection from my SSIS server through to the DB server (using the sa username/password). I click "Test Connection" and it comes back success. However when I run the DTSX using this ODBC connection it always comes back with "Login failed for user ''. The user is not associated with a trusted SQL server connection".
I looked at the event log on the DB server and I have a failure audit there with the same message.
After some Googling I though I may need to set up a user for the connection, so I set up an Administrator user on the DB server and then passed those credentials through as part of the DSN connection, by doing "uid=...;pwd=...;dsn=...;". This came back with "Login failed for user ..."
I then decided passing in the sa username/password...same thing.
Can someone point me in the direction of how to do this...I can't believe this is so complicated. I spent 2 days on this so far try to get data loaded in the DB and it's really frustrating.
Thanks,
Craig
May 17, 2009 at 5:43 am
Hello,
Can you post an example of the details from the relevant SQL Server Log entries? Particularly the “Error, Severity, State” part.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply