So from time to time I will write some code. It isn't all that often and I am not that great at it, but I can get it done normally with only a few drinks, clumps of hair and broken objects afterwards. I have graduated over time to C# because it helps if a BI professional can read a developer's poorly written C# code. Today I was dinking around teaching myself how to use the super simple "Web Site Administration Tool". It is a fairly basic interface that allows amateurs like me to add security without too much fuss. Well I was trying to point this app to use a database backend and could not for the life of me get it to work. So in hopes of saving you some time I will show you how I fixed it. Here is the error message:

There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store.

The following message may help in diagnosing the problem: An error occurred while attempting to initialize a System.Data.SqlClient.SqlConnection object. The value that was provided for the connection string may be wrong, or it may contain an invalid syntax. Parameter name: connectionString

This message of course is bullshizzle because I am using that same connection string to connect my webservice to obtain information. I read all the articles, post and every developer on the planet and I couldn't find a solution. So I had to (GASP!) figure it out for myself. As I suspected I felt pretty stupid when I figured it out, but I will post my solution so you don't spend nearly as much time before you feel stupid. Then again it could be said that if the connection string is good enough for the OleDBConnection object it should be fine for this too. I have wasted too much time to actually sit here and think about this little paradigm anymore, so with all the time you save by reading my solution you can put some thought into this. Just hit digg on your way out. Without further ado, here is the solution.

<connectionStrings>

<remove name="LocalSqlServer"/>

<add name="LocalSqlServer" connectionString="Data Source=blah\devdb;Initial Catalog=superdb;Persist Security Info=True;User ID=User1;Password=nunyabusiness;" providerName="System.Data.SqlClient"/>

<add name="SuperDBConnectionString" connectionString="Data Source=blah\devdb;Initial Catalog=superdb;Persist Security Info=True;User ID=User1;Password=neveryoumind;Provider=SQLOLEDB;" providerName="System.Data.SqlClient"/>

</connectionStrings>

So here are my connection strings, as found in the web.config for those of you playing along at home(the passwords and databases have been changed to protect the innocent, duh!). The first connection string is the one that works and the second is the one I was using for everything else. It seems that the SqlMembershipProvider chokes when you have the clause, "Provider=SQLOLEDB;". You don't need it, so if you leave it out it should work. I have it because I do switch back and forth between other oleDB providers, but I will cross that bridge later. The fact that it says larger than life "providerName" is not lost on me, but if you use this connection string with an OleDBprovider it doesn't seem to care whether you have that there or not. In fact I have changed it to a MySQL database just to see if it would work and it did. I know you aren't reading still but if you are, Thanks.