SQL Alias in Connection String

[Guest]

Hi all

I have an ASP.NET application accessing a SQL named instance that runs o
the web server. To allow easy deployment from the development environment t
production, a SQL alias "My_Alias" has been set up using the Client Networ
Utility. The alias is configured to use a Named Pipes connection pointing t
the named instance. Both EM and isqlw recognise My_Alias

The application attempts to connect as follows
<snip
Dim myConnection As SqlConnection = Ne
SqlConnection(ConfigurationSettings.AppSettings("MyConnectionString")
</snip

where the connection string is defined in Web.config
<snip><add key="MyConnectionString
value="Server=My_Alias;Trusted_Connection=true;database=MyDatabase" /></snip

This fails with error "System.Data.SqlClient.SqlException: SQL Server doe
not exist or access denied

If I replace "My_Alias" in the connection string with the instance nam
"MyServer\MyInstance" all is well. Why is it failing with the alias? I hav
tried an alias name without an underscore, and also tried explicitly settin
the network library to named pipes in the connection string. Have also foun
that My_Alias will work if I configure it for a TCPIP connection... but
want the efficiency of named pipes because client and server are the sam
machine

Microsoft .NET Framework Version:1.0.3705.0; ASP.NET Version:1.0.3705.
Windows 2000 Server SP
SQL 2000 Standard SP

 

[David Browne]

Hi all,

I have an ASP.NET application accessing a SQL named instance that runs on
the web server. To allow easy deployment from the development environment to
production, a SQL alias "My_Alias" has been set up using the Client Network
Utility. The alias is configured to use a Named Pipes connection pointing to
the named instance. Both EM and isqlw recognise My_Alias.

The application attempts to connect as follows:
<snip>
Dim myConnection As SqlConnection = New
SqlConnection(ConfigurationSettings.AppSettings("MyConnectionString"))
</snip>

where the connection string is defined in Web.config:
<snip><add key="MyConnectionString"
value="Server=My_Alias;Trusted_Connection=true;database=MyDatabase"

This fails with error "System.Data.SqlClient.SqlException: SQL Server does
not exist or access denied"

If I replace "My_Alias" in the connection string with the instance name
"MyServer\MyInstance" all is well. Why is it failing with the alias? I have
tried an alias name without an underscore, and also tried explicitly setting
the network library to named pipes in the connection string. Have also found
that My_Alias will work if I configure it for a TCPIP connection... but I
want the efficiency of named pipes because client and server are the same
machine.

My guess is that you need to add some permissions to the account running the
web site (ASPNET by default). Named pipes is an authenticated protocol
(unlike tcp/ip), and so the account may not have permission to use the pipe.
To test this, add ASPNET to the Administrators group (remember to remove it
later), or test the connection string from a console application.

David

 

[Miha Markic]

Hi.

I think that David is partially right.
Aspnet account is the cause but not in connection with named pipes.
It simply has no access to database.
You have several options:
- give aspnet account access to database
- run asp.net app under different account
- connect to sql server using sqlserver authentication

 

[Guest]

David & Miha,

Thanks for your replies. I have made ASPNET a member of local admins and rebooted the server to leave no doubt the change had taken effect (ASPNET already had datareader on the db). Confirmed that ASPNET can connect by specifying MyServer\MyInstance directly in the connection string. SQL profiler showed that ASPNET was querying the db.

But the named pipes alias still doesn't work in the conection string (alias still works from isqlw). What else could it be?

 

[David Browne]

David & Miha,

Thanks for your replies. I have made ASPNET a member of local admins and

rebooted the server to leave no doubt the change had taken effect (ASPNET
already had datareader on the db). Confirmed that ASPNET can connect by
specifying MyServer\MyInstance directly in the connection string. SQL
profiler showed that ASPNET was querying the db.

But the named pipes alias still doesn't work in the conection string

(alias still works from isqlw). What else could it be?

Hmm, I don't know.

Perhaps we should just let it go, and specify the connection library in the
connection string. Like this:

Dim cc As New SqlConnection("Network Library=DBNMPNTW;Data Source=[netbois
name];Initial Catalog=xxx;User ID=xx;Password=xxx")


At least see if that works, and if your connection is really using Named
Pipes.

David