MSSQL Express 2008 connection failure

Post here for help with installing or upgrading mojoPortal pre-compiled release packages. When posting in this forum, please provide all relevant details. You may also want to review the installation or upgrading documentation.

If you have questions about using the source code or working with mojoPortal in Visual Studio, please post in the Developer forum.

Post here for help with installation of mojoPortal pre-compiled release packages

When posting in this forum, please try to provide as many relevant details as possible. Particularly the following:

  • What operating system were you running when the bug appeared?
  • What database platform is your site using?
  • What version of mojoPortal are you running?
  • What version of .NET do you use?
  • What steps are necessary to reproduce the issue? Compare expected results vs actual results.

You may also want to review the installation or upgrading documentation.

If you have questions about using the source code or working with mojoPortal in Visual Studio, please post in the Developer forum.

This thread is closed to new posts. You must sign in to post in the forums.
3/10/2009 12:56:39 AM
Gravatar
Total Posts 4

MSSQL Express 2008 connection failure

 Getting the following on Windows Vista Ultimate (X64) with MSSql Express 2008 and current version of mojoPortal.

System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified).

Looking at the SQL log file, the SQL Server is only offering a 'pipe' connection and not a TCP/IP port and remote connections are allowed.

Anyone know how to get MSSql Express 2008 to offer a TCP/IP port connection?  Might the firewall be hosing me up?

I know it is possible because mojoPortal/MSSql Express is working correctly on the hosting site (WebHostForASP.NET) and the connection string is:

<add key="MSSQLConnectionString" value="server=75.125.253.50\SQLExpress,1750;......

I'm guessing 1750 is the port that SQL Server is listening on.

Thanks, Craig

 

3/10/2009 7:00:50 AM
Gravatar
Total Posts 18439

Re: MSSQL Express 2008 connection failure

Hi,

It seems your connection string must not be correct. 

I assume the Vista machine is your local machine and your mojoportal is running on the same machine as SQLExpress so its not a remote connection issue. Your local machine should have the connection string different from the one on webhost for asp, it will have localhost instead of the ip address, I would remove the port from the connection string unless you configured your SQLExpress to run on a custom port its not needed. If your SQLExpress installation is a default instance instead of a named instance then you would remove the \SQLExpress from the connection string and just use localhost ie the server name. If it is installed as a named instance but has a different instance name than "SQLExpress" then you need to replace \SQLExpress with \WhateverYourInstanceNameIs.

I recommend use a sql user not a windows user for your connection string, so you have to make sure SQLExpress is configured for both Sql Authentication and Windows Authentication. Open SQL Server Management Tools and right click the top node and choose Properties, on the Security section you can enable this. Then create a new sql user and put it in the dbo role for the mojoportal db and then specify this user in your connection string. You can also review the Connections section whether to allow remote connections but really if its on the same machine its a local connection.

Since its a local connection I don't think the firewall is at fault but I don't rule it out either.

Hope it helps,

Joe

3/10/2009 11:37:50 PM
Gravatar
Total Posts 4

Re: MSSQL Express 2008 connection failure

Thanks, Joe.  Hope you liked that beer.

And yes that is exactly what I was up to.  Trying to get mojoPortal/MSSQL Express 2008 working on my laptop.

Anyway, the connection issue the MSSql Express 2008 has been resolved and here is the fix.

First the default install of MSSQL Express 2008 has TCP/IP disabled.  To change this default, do the following.

STEP 1: Enabling TCP/IP

First we must tell SQL Server Express to listen on TCP/IP, to do this perform the following steps:

  1. Launch the SQL Server Configuration Manager from the "Microsoft SQL Server 2008" Program menu
  2. Click on the "Protocols for SQLEXPRESS" node or the specific instance name of choice.
  3. Right click on "TCP/IP" in the list of Protocols and choose, "Enable"
  4. Restart the MSSQL server service(s).
  5. With PowerShell do: PS C:\ > stop-service mssql* -force
  6. With PowerShell do: PS C:\> start-service 'MSSQL$SQLEXPRESS'

(reference: http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx)

In my case, the connection string in user.config was updated to...

  <add key="MSSQLConnectionString" value="server=localhost\CADAYTON;.../>

Note that Express 2008 uses dynamic port values, so don't expect the port number to remain the same unless it is some how made to use a static port numbers.   Use SQL Server Management Studio to review the logs if your interested seeing which port is being offered for current running server process.

The second problem MSSQL Express 2008 by default will only do Windows authentication.  Did the following to remedy this issue.

  1. Started SQL Server Management Studio GUI and select 'File>Connect Object Explorer...'
  2. Right clicked on the SQL instance name in the navigation pane and selected 'Properties'.
  3. In the Properties dialog, selected 'Security' in the naviation pane and then checked 'SQL Server and Windows Authentication mode'.

The last step was to create a SQL Server account to match the account name and password specified in the connection string of the file user.config. The account was also given db_owner permission.

All the software is happy now and I now have a few more gray hairs.

Hope this helps someone avoid a few gray hairs.

Joe you have done an outstanding job of putting mojoPortal together.  I'm looking forward to doing a deep dive on it.

-Craig


 

 

You must sign in to post in the forums. This thread is closed to new posts.