I’ve created a tool to help test different connection strings and settings for the various databases. The information out there on certain database connection types is poor at best so this was put together over a week long process of testing and reverse engineering. A lot of forums and blog posts were pieced together to figure all of this out.

In the process of figuring all of this out, I ended up writing a tool to help me quickly test different scenarios and configurations. I called the tool “ConnectionTester” and I’m making it available with source code so you can use it to do your own testing in your environment and look at the source code to see how I did certain things. Here is what the tool looks like:

One of the really useful features of this tool is that it allows you to run under any account on Windows and impersonate a different account on any machine or domain so you can really see if your windows authentication is working or not. The tool guides you based on the database type and connection provider you select. It shows connection strings in ODBC and the actual .NET provider for each database. You can even subvert all the settings and just type in a connection string directly so you can quickly try different formats. Finally, you can run an optional SQL SELECT query in the process to really make sure you are hitting the right database and settings. You can download the binary and code for this tool here:

ConnectionTester.zip

Once the ConnectionTester is run, you can see the output as it attempts to connect. You can also see and copy/paste the connection string it generates for you so once things are connecting correctly, you can easily just copy this connection string into your Web.config or App.config file to get your application running quickly. Here is a sample of the output of the application (private info has been marked out for security):

I tested against Oracle 11g, MySQL 5.0, and Microsoft SQL Server 2005 for these connection strings but the concepts and strings should be relatively the same for other databases. The following is a list of each connection type and a sample connection string:

MySQL Windows Authentication Problem

As of this writing (March 2009), MySQL currently does not support Windows or LDAP authentication. There is work that the MySQL team is doing currently on making a pluggable authentication module that will allow third parties to build tools that can authenticate against LDAP or Windows Active Directory but this module is not available yet.

Oracle Windows Authentication Setup

The only other thing to mention here is that getting Oracle working with Windows Authentication is not an “out of the box” easy process. I found a great blog post here that describes how to get connected and set up your windows account to connect to Oracle:

http://oradim.blogspot.com/2007/11/configuring-windows-authentication.html

NOTE: One thing to remember in Oracle 11 is that your user name and password are cap-sensitive (unless you turn this feature off in Oracle) so if you get an invalid login/password error, look at that first.

Getting the .NET Data Providers

To run the application, you will also need the .NET data providers for each database installed on your machine. Here are the links to the data providers I used:

MySQL .NET Data Provider (Connector/Net 6.0)

http://dev.mysql.com/downloads/connector/net/6.0.html

Oracle .NET Data Provider (Oracle 11g ODAC and Oracle Developer Tools)

http://www.oracle.com/technology/software/tech/dotnet/utilsoft.html

There is a good Wiki entry on different ways to connect to Oracle that was very helpful as well. You can see that here:

http://www.installationwiki.org/ODP.NET_Getting_Started_Guide