SQL Server remote connection problem

[JScannell]

I have a website installed on my PC that needs to run SQL queries on a remote SQL server. This process worked perfectly on my recently departed XP computer.

Now I have a Windows 7 Professional computer. First, I installed SQL Server 2008 Express and the mgmt. studio. I have installed and am using IIS 7.5 successfully for my other local websites.

They connect to my local SQLEXPRESS with no issues.

Prior to doing the property changes below, I got a general connection error when I attempted to run a query on my remote SQL server.

In order to connect remotely, I was instructed to run Sql Server Configuration Manager and Enable the TCP/IP protocol. I discovered two different TCP/IP protocol settings. One in "SQL Server Network Configuration | Protocols for SQLEXPRESS" and one in "SQL Native Client 10.0 Configuration | Client Protocols"

Not knowing which one the article was referring to, I enabled both of the TCP/IP settings.

Now I get a "can't find table aaaaa" error when I run my website and attempt a query.

What is going on with this thing? SQL Server Express 2005 didn't have any of these issues.

Thanks in advance,

Jerry Scannell

 

[simian336]

When you created you dsn to connect did you change the database to match the database where your table is stored?

And did you test the DSN when you finished?

Simi

 

[JScannell]

I'm not using a DSN. I am using a connection string like this:

Provider="SQLOLEDB"; data source=[remote server address]; initial catalog=[database name];[username and password]"

Jerry Scannell

 

[simian336]

So, initial catalog=[database name]; is correct?

You might also want to create a DSN just to verify that your info is correct and make sure it connects.

Simi

 

[JScannell]

The info is correct. I can connect to it via mgmt. studio, plus that's the connection string I've used for several years. That string works fine on the actual website.

So it's some kind of issue with my SQLEXPRESS configuration when SQLOLEDB is used for a remote connection other than enabling the TCP/IP settings.


Jerry Scannell

 

[Alan0568]

As sql express typically installs as an instance .... servername/sqlexpress

you must have the 'SQL Server Browser Service' running.

Also within server properties > security make sure you have the correct 'server authentication' mode. Eg if you are passing a username/password in your string you will need it to be 'Sql Server AND Windows authentication mode'

 

[Alan0568]

Forgot to add, you will need to restart the sql server service itself after the changes.

 

[JScannell]

Alan,

Thank you --- thank you --- thank you.

It was the 'SQL Server Browser Service' . I've never even heard of this service so it was disabled, since I never would have known about it. So it is working fine now.

Thanks,

Jerry Scannell