I downloaded an ASP.NET web application from the web to learn the .NET framework. I was successful in running the website on my laptop using ASP.NET Development Server that comes with Visual Studio 2005. My laptop is running Windows XP Professional and SQL Server 2005. I am now trying to do the same on my desktop computer running Windows Vista; however, for some reason i am getting an error when the website tries to connect to the DB. Here are the steps i have followed so far:
1. I tried to configure the website DB connection the same way i configured it on my laptop. Basically I use the following connectionStrings:
<connectionStrings>
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="Data Source=localhost;Initial Catalog=TheBeerHouse;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings
2. That same connection string worked fine on my laptop. But I got the following error on my desktop:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
I am wondering if the error is related to Vista. When i try to connect to that DB using SQL Management Studio via Windows Authetication and do not run it as Administrator then I cannot connect to the DB. In other words, only when i run SQL Management Studio as administrator i am able to use Windows authentication to connect to that DB.
3. Next, I tried creating a SQL login. I made sure I can use that login to access the DB directly using SQL Management Studio. In addition, I changed the database to allow "Local and remote connections" "Using both TCP/IP and named pipes".
4. Changed the connectionStrings in web.config to:
<connectionStrings>
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="Data Source=localhost;Initial Catalog=TheBeerHouse;Integrated Security=False;uid=*****pwd=****" providerName="System.Data.SqlClient"/>
</connectionStrings
Obviously, the actual connectionString has the actual user name i created and the correct password instead of (*) asterisks.
5. And once again, I got same error as above
Any help will be appreciated!
Hi,
Is sqlserver on your desktop running in "Windows Authentication Mode" or "Mixed Mode"
Open up SqlServer Management Studio, right click on the Server, not the database(s), then in the left hand menu shown select Security. The dialog box on the right should have"SQL Server and Window Authentication Mode" checked instead of just"Windows Authentication Mode".
Hope this helps
Regards
ScottyB
|||I have "SQL Server and Window Authentication Mode"checked. I can login directly to the database via SQL Managament Studio using the same login and password as specified in the connectionString. I also have allowedLocal and remote connections and haveUsingboth TCP/IP and named pipeschecked.
|||
Hi nj07208
I have just noticed in the connection string that you posted here that you do not have a semiclon after the uid and pwd parameters. You can try this. See the listings below
Original Connection String
<connectionStrings> <remove name="LocalSqlServer"/> <add name="LocalSqlServer" connectionString="Data Source=localhost;Initial Catalog=TheBeerHouse;Integrated Security=False;uid=*****pwd=****" providerName="System.Data.SqlClient"/> </connectionStrings>
Modified Connection String
<connectionStrings> <remove name="LocalSqlServer"/> <add name="LocalSqlServer" connectionString="Data Source=localhost;Initial Catalog=TheBeerHouse;Integrated Security=False;uid=*****;pwd=****"; providerName="System.Data.SqlClient"/> </connectionStrings>
Regards
ScottyB
|||i guess you meant with the semicolon right after the password before the double quotes. I tried that and it didn't work =(
|||I got a new error now! I turned off my firewalls (both Windows and McAfee ViruScan) and now i get a new error:
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
Any ideas?
If you have access to the iismanager try doing a pool reset or a iisreset.
Do you have any code in your application that goes through a loop and opens connections. This error may suggest you are opening connections faster than the garbage collector can dispose of them.
Regards
ScottyB
|||I am using ASP.NET Development Server that comes with Visual Studio 2005. I am not using IIS.
I got it!!! It works now!!! On the connectionString i changed Data Source from localhost to localhost\SQLEXPRESS. This is needed because I am using a named instance. So, to recap for anyone who is interested, these are the steps i followed:
1. EnableSQL Server and Windows Authentication mode. This can be done from SQL Management Studio
2. Create SQL Server login. Make sure you can login to the database using SQL Management Studio
3. EnableLocal and remote connections and haveUsingboth TCP/IP and named pipeschecked. This can be done from SQL Server Surface Area Configuration.
4. Connection String on web.config should be the following
<connectionStrings> <remove name="LocalSqlServer"/> <add name="LocalSqlServer" connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=TheBeerHouse;Integrated Security=False;uid=username;pwd=password;" providerName="System.Data.SqlClient"/></connectionStrings>Note that I enabled both Windows and McAvfee AntiVirus firewalls and it still works.
