Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

A VB.NET component delivered following message:

Login failed for user MyDBUser

It is about an OleDbConnection.

btw The user works fine togehter with SQL Server Management Studio.

Retaining a password via the connection manager does not work even with enabling Save my password. In my case, it is not a security issue to retain the password because the SSIS package will not be published for others. Hence, what's the fastest way -concerning the VB.NET code- to enable an access to the (SQL Server) password?

EDIT: Workaround: A SSIS variable was attached to the scope of the required control flow. Me.readVariable was applied to concat the connection string.

share|improve this question
add comment

2 Answers

Given your symptoms, I believe you have changed your PackageProtectionLevel from the default setting of EncryptSensitiveWithUserKey to DontSaveSensitive This is a good thing in my world view but it does require that you now manage sensitive data.

The hackish approach you are using is to create a password Variable and then use that in an Expression as part of the Connection String. That works but you are now storing a password in clear text which is what the default PackageProtetionLevel was designed to protect against.

Fixing it "right" in my mind would involve storing that password as an external, secured resource. We always used SQL Server configurations in our packages and extended the base table with a column indicating whether this configuration was sensitive or not. In production, we, the developers, only had access to rows that were set as insensitive. The service account which ran all the packages had no such restrictions.

Alternate approaches are to store these configuration values in an XML file, registry key, environment variable or a parent package.

You can also skip the configuration route if you know your packages will only ever be launched through method X (batch file, Agent Job, etc). In that case, simply provide the password as a run-time value to the invoking process and all will be well.

share|improve this answer
add comment

Typically you'd need to add some items into the connection string used by the SQL Server client in VB.Net

This connection string would be something like (lines wrapped for readability):

network=dmmssocn;server=MySqlServer.mydomain;Integrated_Security=false;
User=MyUserName;Pass=MyPassword

The above string works if you are not using integrated Windows Authentication. If you ARE using integrated auth (it sounds like you AREN'T), then use something like:

network=dmmssocn;server=MySqlServer.mydomain;Integrated_Security=true;

http://www.connectionstrings.com/ is an excellent resource for all things related to connection strings!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.