MySQL Bugs: #36306: .My SQL .Net Provider Does Not Store Password

Bug #36306	.My SQL .Net Provider Does Not Store Password
Submitted:	23 Apr 2008 22:18	Modified:	11 Aug 2008 20:58
Reporter:	John Davis	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	5.2.1	OS:	Windows (XP, Windows Server 2003)
Assigned to:		CPU Architecture:	Any
Tags:	.net, .net Connector, password

Description:
The password for the .Net connector in Visual Studio 2005 is not saved when creating a connection in SSIS packages in Visual Studio 2005.

How to repeat:
Open Visual Studio 2005. 
Create or open any Business Intelligence project. 
Create a new ADO.Net connection and select the MySQL .Net Connector. 
Set the connection properties and save.
Add a datareader source.
Connect to a file connector.
Run package.
Error message returned saying "access denied to User...."
Check the previously saved connection. There's now password saved.

Suggested fix:
Make it save the password.
Possible workaround: 
Go into the XML of the package and modify the properties at the XML level.

Did you add 'persist security info=true' to your connection string?

No. The definition of that parameter (I thought) was to obtain/persist info about the connection at runtime so I didn't try it. I'll give it a go right now.

Setting the persist info to TRUE didn't help password still disappeared. I was hopeful though.....

Can you outline what products you are using to reproduce this bug?  We would like to get step by step directions if that is possible.

I'm using Visual Studio 2005 Business Intelligence Studio to create a SSIS - Integration Services project. I create an ADO.Net connection using mySQL Connector 5.2.1. I set the password at design time and run the package. If I run the same package again with no changes, the password disappears.

You can view the XML of the package by right clicking on it in the solution explorer and then left clicking the "View Code" option. Do a find for the Password element of the mySQL.net connection. You can actually key in the password in the password element and the package will start working again. If you make any changes you can see the XML refresh and the password element is empty again!

Any news?

Hi John.

Verified as described. It's even easier than you explained. After new connection wizard starts and one chooses MySQL c/NET as provider and fills in all properties, clicking "Next" button shows preview of connection string *without* password (even though one is passed in configuration screen as well as "PersisSecurityInfo=True"):

Preview:

  Connection string

  Character Set=latin1;Database=test;Server=localhost;Use Old Syntax=False;Password=;Persist Security Info=True;User Id=root

Erroneous connection string

Attachment: Bug36306.JPG (image/jpeg, text), 29.04 KiB.

How close are we to a solution?

How close to are we to a solution? Any new ideas?

John, I really don't have any new info. Bug is still waiting to be picked up by devs. I do not see it as "Critical" as you can always work around it.

This one IS critical because it is being used in an automated situation where human intervention isn't a option.

Any news?

John, I will notify Reggie now.

John

I just tried this with 5.2.2 and it worked great for me.  Here's what I did:

1.  Create new Integration Services project
2.  Add data source to a locally running server using a non-root userid and password.  Used the mysql client to make sure those credentials work.  I set persist security info to true
3.  Added a connection manager for my new data source
4.  Added a dataReader source and told it to use my data source (it was named localhost.test.reggie)
5.  Added a flat file destination, set it's properties, and connected the two
6.  Ran the project
7.  My text file was created with all the data from my table without any difficulty.

Can you make sure the user id/password you are trying to use works ok from the command line client?  Also, can you try again with 5.2.2?

Setting this to can't repeat until I hear back from the user.  As I said in my last post it worked great for me.

The key to this problem is repeated execution. When the package is executed once, it works but when you execute it again without changing anything, the password disappears.

Also I was going from MySQL to SQL 2005.

John

Can you give me more detailed steps?  How do I run the SSIS task more than once without shutting it down?  I ran my task once with the debug button, shut it down, then run it again.  No problems.  Can you zip up your project and email it to me at reggie@mysql.com?

I can't send the package as it was something I built for a previous employer. You don't have to stop the package. Just let it finish and the start it again. I actually saw the xml section with the password disappear when I executed the package.

Hey, I have a similar problem. I use visual studio 2005. I have installed MySQL Connector Net 5.5.3. I can see the table and the data contained in my MySQL database 4.1.11. I use a data flow task that contains a datareader source that is connecting to my database with a mysql data provider where i enter the database name, userid, password and database name. And when I run I got the error   « MySql.Data.MySqlClient.MySqlException: Access denied for user 'aaa'@'xx.xx.xxx.xxx' (using password: NO) 
And in the properties of my connection the password has disapeared. And the persist security info value is true.

I note there was no final solution to this problem on the last post... so I offer mine.

I had this problem also with the .Net 5.1.5 on Windows server 2008 running SQL Server 2008.  All of a sudden every connection I had stopped working, saying Using password: NO, even though every connection actually had a password.  It was exactly the same John Davis' post where the password wasn't being stored regardless of what you did.

I eventually found that the visual studio solution must have been corrupted.  I copied the solution from the production server back into the development server, ensured the connections had the username/password.  Ran the package and the problem was resolved.

Thanks for your info Jeff. This kind of things happening is almost impossible to trace and most of the people just dismiss such possibility...

FYI- i tried all workaround as mention above but no luck... I think its a critical issue.

I have tried with BIDS 2008 and there was no real solution for this issue. Please, MYsql team look into this issue.

Here is the steps

Created Package,which transfer data from Mysql - SQL 2008 db.
Package run fine at BIDS level but when i deploy to SSIS package store or File system and try to execute package i got an error.
environment information - win2008- 64 bit, bids 2008, myql .net connector for 32 bit(Connector/Net 6.4.4) and mysql odbc connector for 64 bit(Connector/ODBC 5.1.9) but having error as following:

Description: MySql.Data.MySqlClient.MySqlException: Access denied for user 'Username'@'sever' (using password: NO)     at MySql.Data.MySqlClient.MySqlStream.ReadPacket()     at MySql.Data.MySqlClient.NativeDriver.AuthenticateNew(Boolean reset)     at MySql.Data.MySqlClient.NativeDriver.Authenticate(Boolean reset)     at MySql.Data.MySqlClient.NativeDriver.Open()     at MySql.Data.MySqlClient.Driver.Open()     at MySql.Data.MySqlClient.TracingDriver.Open()     at MySql.Data.MySqlClient.Driver.Create(MySqlConnectionStringBuilder settings)     at MySql.Data.MySqlClient.MySqlPool.CreateNewPooledConnection()     at MySql.Data.MySqlClient.MySqlPool.GetPooledConnection()     at MySql.Data.MySqlClient.MySqlPool.TryToGetDriver()     at MySql.Data.MySqlClient.MySqlPool.GetConnection()     at MySql.Data.MySqlClient.MySqlConnection.Open()     at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)     at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction)     at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)     at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)  End Error.

Any help?

Mit

Hi, I have the Same problem.
everything was working well, but since 2 days ago, the password is not stored.
I have exactly the same error message.

it strange, because it was working before.

MYsql COnnector Net 6.5.4

Windows Server 2008
SQL SERVER 2005 SP4.

I have seen some windows update have been installed on my Server.
maybe it is the reason of my new problem...
Is there a path, solution or anything else than could solve this basic problem of password Staorage ?

Thx.

The same problem is with VS 2010 SQL Server 2012. Connector/Net 6.8.3. 
Unbelievable....

I have solved this problem just adding connection string with password using ConnectionManager  expressions

Hi,

I am facing the same issue with BIDS 2008 R2,
When I try to set the value of some properties of the MySQL data provider to True, nothing happens and the property remains blank e.g "Allow Batch", "Persist Security Info", "SsI Mode", "Connection Reset",...

The username and password won't be saved at all.
We need to quickly decide if we want to use MySQl or SQL Server, with this bug we can't use MySQL.