SQL Server MSDE Separate Instance ASP.NET & DSN Connections

Today I ran into an issue where I had a bit of difficulty connecting ASP.net to SQL Server MSDE Named Instance. The problem is that the website previously did not need to specify a named instance of MSDE/SQL Server. I was able to address the connection over asp rather easily, but there was a bit of development already done in ASP.NET and I was not very familiar with the ASP.NET setup.

A little bit of background on myself... I'm a newbie to .NET, but I am always willing to learn. I've been coding in VBscript (ASP), SQL, PHP, XML/HTML, and Javascript for quite some time. I've even had the opportunity to work on some actionscript / flex projects incorporating calls between dhtml and simple flex applications. So you may laugh at how basic this little bit of info is, or (I hope) you may actually find a useful tidbit of knowledge.

I had been handed over a web site from a previous programmer/software company and I had to figure out a way to deploy the website rather quickly. We went with a Dedicated Machine on Win2k3 Web Edition. I knew that a lot of the other O/S I ran in the past had issues with too many unneeded services (Win2k3 SBS I'm looking at you!). So to help client keep costs low and through previous experience (and knowledge about web traffic) and requirements of site, I decided that using MSDE would be a sufficient solution for the database.

I am familiar with SQL Server Enterprise Manager and SQL Server Query Tool. In using MSDE, I didn't think through that I wouldn't have a GUI if I used MSDE. This lead me through the maze of what is IT and Dev...

So yeah, I called some friends and apparently you can run MSDE and just install SQL Server Tools on a machine to get GUI access. Lucky for my client (and me), I have a legitimate copy of SQL Server I bought for my business. But here's the kicker... Web Edition 2k3 doesn't allow you (due to its licensing) to install SQL Server on the same machine. So now I was back to square one. Eventually I figured out how to use SQL Server 2005 Express Edition to serve as the gui for the MSDE DB I had already created. Don't ask me why, but I didn't feel like porting over to a new instance of 2005 by this point.

Guess what though... you cannot import (at least I haven't figure out yet) data the same way you can with Enterprise Manager. The functionality is significantly limited from Enterprise Manager for importing and exporting data. I have tried to connect to MSDE Instance via tcp ip, but I think my isp seems to be blocking port 1433/1434 udp... but that is a topic for another post I suppose.

so the basics...
1) check web.config file where you have the dsn / application connection string stored. It seemed that ASP.NET was not connecting. After a bit of debugging I realized that it should be connecting to a different instance of SQL Server.

2) in the web config - this can also be edited through IIS - ASP.NET tab...

previously looked something like this:
add key= "DSN16" value="server=LOCALHOST;database=DBNAME;uid=sa;pwd=XC123XC123x;"/

3) Now change it to
add key= "DSN16" value="Server=COMPUTERNAME\INSTANCENAME;Database=DBNAME;uid=sqlserverlogin;pwd=XC123XC123x;"/

One thing I would recommend is that you try to login with SQL Server only credentials when you are logging in through Enterprise Manager or SQL Server Manager 2005. It took me a long time to figure this out, but if you know that you can login with the SQL Server only credentials, they should hypothetically work when ASP.NET is using these credentials to talk to SQL Server.

Oh yeah, another thing is that I had to look up on SQL Server properties to give me a clue to use COMPUTERNAME\INSTANCENAME. Since I had to figure it out on my own and I couldn't find the info online ... I did find about connections with named instances, but without realizing that the credentials i was using weren't SQL server credentials... well it was useless. until I switched to SQL server only credentials, then it worked.

hope this helps and saves you a bit of time. Now please click on a google ad.
thanks,
Mauricio