Remoting Host in IIS - SQL Server 2005 - SQL Server does not allow remote connections.

brettswift posted on Thursday, October 19, 2006

This is a KB article that I tried with no luck:

http://support.microsoft.com/kb/914277

The error I get is in red on that page:

Sqlcmd: Error: Microsoft SQL Native Client: 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.

I have a windows app using the CSLA 2.0 framework, setup to use IIS and remoting.

My application works fine locally, but when I install this on a remote machine (via click-once deployment) I get the error above.

I have this running on a SQL Server 2005 machine, Win XP Pro (sp2), and behind a d-link 614+ router.

I have enabled both the remote connections and the browser service.. I haven't checked my router settings but this shouldn't be the problem - because its already getting through the firewall.. I'm suprised that KB article would make a difference either.

Is it some kind of authorization I have to give the ASP_NET account that IIS uses, to access the SQL Server ?

ajj3085 replied on Friday, October 20, 2006

Did you restart Sql Server after changing the settings? I think you have to for the new settings to take effect.

Are you using integrated security=sspi? If so, and you have configured impersonation in iis, then yes, you'll need to allow the aspnet worker account access... but there's a cavet. If IIS and Sql are on different machines, you won't be able to do this, since aspnet user is a local account. You'll have to either create a domain account, or not use sspi.

ajj3085 replied on Friday, October 20, 2006

As I said, to do that Sql and IIS need to be on the same machine. If they are, you just add aspnet as a login to the database server, then set it up as a user in the database you wish it to access.

FWIW, I recommend that you setup database roles, and assign permissions to them. Make it easier to add a login and user.

Andy

brettswift replied on Friday, October 20, 2006

I'm just running this at home so they are on the same machine. I've been busy with work so haven't looked at this app in a while.

Will have to test this out when I get to work but I think this should work. Thanks Andy.. I'll let you know if I have any problems.

I also setup the anonymous user in IIS to be MACHINENAME\ASPNET instead of IUS_MachineName

brettswift replied on Friday, October 20, 2006

I'm at work, just tried it. It is now giving me an error saying that it can't locate the sql server instance, but also that remote connections may not be enabled. (I know they are enabled because I just did that last night and rebooted my machine!).

hmm. Why would it not be able to see the sql server?

brettswift replied on Friday, October 20, 2006

Ok, now i'm getting an error where it says that "Login failed for user ''. the user may not be associated with a trusted sql login" or something like that.

my connection string looks like this:

<add name="Security"
connectionString="Network=DBMSSOCN;Data Source=192.168.0.101\SQLEXPRESS;Initial Catalog=HorseTracker;Integrated Security=SSPI;User ID=user;password=pass;"
providerName="System.Data.SqlClient" />

I also had the port on there that was in the sql server config utility, but that didn't work either.

anonymous access user in IIS has an account in sql server, sql browser is on (which is only needed if i don't supply the port) and so are remote connections. hmmm

Why would it see my username as blank?