Connecting WroxUnited DB's in SQL Server 2000

TritonOps · December 21st, 2005, 11:05 AM

Hello All,

I'm new to web development in general and new to ASP.NET.

I'm using the Beginning ASP.NET 2.0 book and I have installed Visual Web Developer to use with it. I did not install SQL Server 2005 Express however because I already have SQL Server 2000 Developer Edition on my computer.

I setup the WroxUnited site via IIS (5.1), however now when I try to connect to the site I get the following error.

Code:

Server Error in '/WroxUnited' Application.
--------------------------------------------------------------------------------

Configuration Error 
Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately. 

Parser Error Message: Unrecognized attribute 'xmlns'.
Source Error: 


Line 5:       A full list of settings and comments can be found in machine.config.comments usually
Line 6:       located in \Windows\Microsft.Net\Frameworks\v2.x\Config -->
Line 7:  <configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
Line 8:    <!--
Line 9:      Mode defines certain feature availability:
 

Source File: C:\Inetpub\wwwroot\WroxUnited\web.config    Line: 7 


--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573

I'm guessing this is because the WroxUnited App is configured to use SQL Server 2005 Express to connect to the databases (WroxUnited.mdf, ASPNetdb.mdf).

My question is how can I set things up to have it use my SQL Server 2000 Dev. Ed. instead. I'm assuming I will have to attach the WroxUnited.mdf, and ASPNetdb.mdf databases via Enterprise Manager first but after that what would be my next step? It appears the global connection string is located in the web.config file. The code for that reads as thus:

Code:

<!--
        define the connection string to the database
    -->
  <connectionStrings>
     <add name="WroxUnited"
      connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|WroxUnited.mdf;Integrated Security=True;User Instance=True"
      providerName="System.Data.SqlClient" />
  </connectionStrings>

Does this have to be modified in some way? Again this is all new to me so any suggestions or help would be much appreciated.

Thanks in advance!

-Shannon

Imar · December 21st, 2005, 12:07 PM

Hi Shannon,

The error you get is not related to SQL Server, but to the .NET Framework. Your site is configured to run against .NET 1.x, and not 2.0
To fix this, open up IIS, locate your site, right-click it and choose Properties. Switch to the ASP.NET tab and make sure that ASP.NET 2.0 is selected.

Then in your connectionstirng replace this:

Data Source=.\SQLEXPRESS

to the name of your SQL Server. If you installed a default instance then this will do:

Data Source=(local)

If you used a Named Instance, try this:

Data Source=(local)\NameOfYourInstance

HtH,

Imar

TritonOps · December 21st, 2005, 01:04 PM

Hi Imar,

Thanks for the help. I made the change in IIS to ASP.NET 2.0. In my web.config file I then changed the connection string to read:

Code:

<connectionStrings>
     <add name="WroxUnited"
      connectionString="Data Source=(local)SHANNONS\DEVLAB;AttachDbFilename=|DataDirectory|WroxUnited.mdf;Integrated Security=True;User Instance=True"
      providerName="System.Data.SqlClient" />
  </connectionStrings>

The name of my SQL Server Instance is SHANNONS\DEVLAB. Now when I attempt to load the site I get the following error.

Code:

Server Error in '/WroxUnited' Application.
--------------------------------------------------------------------------------

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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Source Error: 


Line 112:    Public Overridable Property Theme() As String
Line 113:        Get
Line 114:            Return CType(Me.GetPropertyValue("Theme"),String)
Line 115:        End Get
Line 116:        Set
 

Source File: C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\wroxunited\80802c5c\69674225\App_Code.dqfsfczh.6.vb    Line: 114 

Stack Trace: 


[SqlException (0x80131904): 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)]
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735027
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
   System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820
   System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
   System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
   System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130
   System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
   System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
   System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
   System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
   System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
   System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
   System.Data.SqlClient.SqlConnection.Open() +111
   System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +84
   System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +197
   System.Web.Profile.SqlProfileProvider.GetPropertyValuesFromDatabase(String userName, SettingsPropertyValueCollection svc) +766
   System.Web.Profile.SqlProfileProvider.GetPropertyValues(SettingsContext sc, SettingsPropertyCollection properties) +428
   System.Configuration.SettingsBase.GetPropertiesFromProvider(SettingsProvider provider) +410
   System.Configuration.SettingsBase.GetPropertyValueByName(String propertyName) +117
   System.Configuration.SettingsBase.get_Item(String propertyName) +89
   System.Web.Profile.ProfileBase.GetInternal(String propertyName) +36
   System.Web.Profile.ProfileBase.get_Item(String propertyName) +68
   System.Web.Profile.ProfileBase.GetPropertyValue(String propertyName) +4
   ProfileCommon.get_Theme() in C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\wroxunited\80802c5c\69674225\App_Code.dqfsfczh.6.vb:114
   Wrox.Web.GlobalEvents.ThemeModule.app_PreRequestHandlerExecute(Object Sender, EventArgs E) in C:\Inetpub\wwwroot\WroxUnited\App_Code\ThemeModule.vb:30
   System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +92
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64

 


--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

This seems to indicate that it cannot find the server/instance specified. I've tried it with a few different combinations in the connection string:

Source=(local)SHANNONS\DEVLAB
Source=(local)\\SHANNONS\DEVLAB
Source=SHANNONS\DEVLAB
Source=.\SHANNONS\DEVLAB
Source=(local)\DEVLAB
Source=(local)

Still the same error with each. I also went into the properties of the SQL Server instance under Connections and changed the Maximum Number of Concurrent Connections option from 0 to 5. I did this because of the message 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. it gives at the beginning of the new error.

Thanks again for your help so far. Any other suggestions?

-Shannon

Imar · December 21st, 2005, 01:10 PM

Is SHANNONS the name of the server and DEVLAB the name of the instance?

In that case, drop the (local) prefix. (local) refers to the local machine and can be used instead of the server name when you connect to a local SQL Server.

So you should use

Data Source=(local)\DEVLAB

or

Data Source=SHANNONS\DEVLAB

HtH,

Imar

TritonOps · December 21st, 2005, 01:38 PM

Yes SHANNONS is the name of the server and DEVLAB the name of the instance.

I've tried both:
Data Source=(local)\DEVLAB

and

Data Source=SHANNONS\DEVLAB

however I get the same error.

One of my concerns is that the error makes reference to SQL Server 2005 as if it thinks it is using SQL Server 2005 and not SQL Server 2000, which is what I have installed. Could this be a problem?

Thanks again.

-Shannon

Imar · December 21st, 2005, 04:30 PM

Now that server is OK, the next thing to check is access and security rights.

First, remove User Instance=True from the connection string. This works only in Sql Express 2005 databases.

Next, make sure the account used by the webserver has access to the SQL Server database. The account used depends on your situation. If you access the site using the built-in web server of VWD, this account is the one you use to log in to your machine. Otherwise, it's the ASPNET account (Windows XP) or Network Service (Server 2003) account.

Add this account to the SQL Server logins and then grant it permissions in your database.

Isn't it *much* easier to install SQL 2005 Express while learning and then later make the move to a full version?

Imar

TritonOps · December 22nd, 2005, 08:54 AM

Quote:

quote:
Isn't it *much* easier to install SQL 2005 Express while learning and then later make the move to a full version?

LOL! It seems so, yes.

Well I tried the other steps you mentioned involving adding the ASPNET user to my SQL Server logins however I'm still getting the same error. For simplicity's sake I think I will just install the SQL Server Express and use that for now to learn with.

Thanks again for all the help. Much appreciated.

-Shannon

Imar · December 22nd, 2005, 06:28 PM

You may want to take a look at this and see if that helps determining the right account and configuring SQL Server:

http://Imar.Spaanjaars.Com/QuickDocId.aspx?QUICKDOC=290

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Kogilaah · September 7th, 2006, 11:29 PM

Hi there everyone. I am new in using asp.net(beginner) and i would like some help in solving my problem here. My problem is exactly what shannon was facing.
I have been following the"try it out" section from the book and am stuck!!!

.
I have already added the sitemappath control and tried running it, and there was an error which says "SqlException was unhandled by user code". and this error message was point towards
"Public Overridable Property Theme() As String
        Get
            Return CType(Me.GetPropertyValue("Theme"),String)
End Get
        Set
            Me.SetPropertyValue("Theme", value)
        End Set
    End Property"
so i assume that there should be some configuration to be done with the sql server.

I believe that since i have downloaded the Chapter03 from the Wrox/Chapter03(coding section). it does not recognize the local server.

Please assist me solve this probelm.
thank you

Kogilaah Pillai