3 server scenario problem

Hi
I'm having trouble connecting to an instance of SQL Server 2005 (Express)
from 2 individual Windows Server 2003 machines running IIS (asp.net 2.0)
I have an IIS on each 2k3 machine, both with identical sites. The idea is
simply that we have a third sql server which is accessed by these 2 websites.
One server gives me a Server login error (user not found:
MYDomain\ServerName), which the second server gives me the usual error 26 -
Cannot connect to Server/Instance specified.
Both IIS apps use the same query string (server=MYSQLSERVER\DARTS;
database=dartsV2;trustedconnection=yes;)
but as I say, one connects, one doesn't! very odd. Anyone got any
suggestions? I've enabled named pipes and tcp/ip on the sql server, along
with "allowing remote and local connections" using surface area config. I've
even setup SQL Browser, which seemed to cure one of the servers (both were
giving me the cannot find instance specified error).
Any suggestions guys?! I'm getting kinda desperate on this one... :o)
Cheers
Dan
Hi,
To narrow down this issue, I have serveral questions which need your
confirmation:
1. Could you ping the SQL Server successfully from the two clients?
2. What will happen if you explicitly add the two domain accounts to the
users group of your third server ?
Also, I recommend you try configuring the client settings as following:
Click the Start menu;
Type cliconfg.exe;
Check if TCP/IP and Named Pipes protocols are enabled;
Select the Alias tab;
Check if the server instance exists in the list;
If it's not existed, click the Add button;
Input the alias name (<servername>\SQLEXPRESS) for Named Pipes protocol and
click OK.
Apply the settings, and then try again.
If you want to use TCP/IP protocol, please first check the listening TCP
port on your server via SQL Server Configuration Manager.
Then add an alias for TCP/IP protocol at the clients. Note that you need to
cancel the selection of "Dynamically determin the port" and input your TCP
port number.
An alias can be different with your SQL Server instance name, but the
server name should be same as your server name.
Please ensure that you can use telnet to access your SQL Server TCP port
before configuring the alias for TCP/IP:
telnet <your server name> <port number>
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi Charles,
Thanks for the reply. I've actually fixed this particular issue, I simply
copied the code with the "user" error onto the other IIS server, so now I
have that error on both. However, I'll post somewhere else for that...
For clarification...
1) yes. I could also use osql (or sqlcmd) to connect to the sql server from
both domain servers, cos they both had sqle installed but no instance
running).
2) I tried that, but it couldn't find the computer accounts when I tried
adding roles etc - presumbly because they were computer accounts and not
users, I'm not sure though?
I have named pipes enabled but this is considered a security risk is it not?
However, I'm not sure how to setup a connection string with an ip address? I
tried server=xxx.xxx.xxx.xxx but it that couldn't connect from either server?
(I have both named pipes and tcp/ip enabled).
Could you point me to an article on how to do that, because if named pipes
are risky, we shouldn't be using them!
Thanks for your reply and help.
Cheers
Dan
"Charles Wang[MSFT]" wrote:

> Hi,
> To narrow down this issue, I have serveral questions which need your
> confirmation:
> 1. Could you ping the SQL Server successfully from the two clients?
> 2. What will happen if you explicitly add the two domain accounts to the
> users group of your third server ?
> Also, I recommend you try configuring the client settings as following:
> Click the Start menu;
> Type cliconfg.exe;
> Check if TCP/IP and Named Pipes protocols are enabled;
> Select the Alias tab;
> Check if the server instance exists in the list;
> If it's not existed, click the Add button;
> Input the alias name (<servername>\SQLEXPRESS) for Named Pipes protocol and
> click OK.
> Apply the settings, and then try again.
> If you want to use TCP/IP protocol, please first check the listening TCP
> port on your server via SQL Server Configuration Manager.
> Then add an alias for TCP/IP protocol at the clients. Note that you need to
> cancel the selection of "Dynamically determin the port" and input your TCP
> port number.
> An alias can be different with your SQL Server instance name, but the
> server name should be same as your server name.
> Please ensure that you can use telnet to access your SQL Server TCP port
> before configuring the alias for TCP/IP:
> telnet <your server name> <port number>
> If you have any other concerns, please feel free to let me know. It's my
> pleasure to be of assistance.
> Charles Wang
> Microsoft Online Community Support
> ================================================== ====
> When responding to posts, please "Reply to Group" via your newsreader
> so that others may learn and benefit from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
|||Hi Dan,
Thanks for your updating and response.
The user error should be caused by the windows logon account at your client
computer not matching the Windows authentication on the server.
Please check if the Windows login account is a member of the Users or
Administrators group under your SQL Server machine. You can maunually add
it to the Users group and then add it to the logins of your SQL Server 2005
Express and assign it permissions in SQL Server 2005 Express.
If you did not install the SQL Server 2005 Management Studio Express, you
can download it from:
http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-
94B8-5A0F62BF7796&DisplayLang=en
This tool provides you an UI to manage your SQL Server 2005 Express like
SQL Server 2000 Enterprise Manager.
If this issue persists, it seems that it is related to the settings of your
web application. You can try using impersonation in your web.config file:
<system.web>
...
<authentication mode = "windows" />
...
<identity impersonate="true" />
...
</system.web>
For more information, you can refer to:
Using sql trusted Connections with asp.net
http://idunno.org/dotNet/trustedConnections.aspx
If you use impersonation, please ensure that the two Windows logon accounts
of your two web application servers must be as a member of the
Users/Administrators group on your SQL Server 2005 machine and be assigned
permissions in your SQL Server 2005 Express instance.
For the second error "Cannot connect to Server/Instance specified", it is
most likely a communication level error. If you are worried about the
security of Named Pipes, you can maually configure the TCP/IP protocol by
following my first reply. Also, you can configure a static TCP port at the
server side. By using SQL Server Configuration Manager, you can specify a
static TCP port under the IP All, and leave other fields under IP1 and IP2
blank:
1. Open SQL Server Configuration Manager;
2. Select Protocols for <instance name>;
3. Double click TCP/IP, switch to the IP Address tab;
4. Specify the TCP Port under the IPAll, leave all TCP Dynamics Ports under
IP1,IP2 and IPAll blank, and leave TCP Port under IP1 and IP2 blank.
5. Click OK and restart your SQL Server service.
On the client machines, ensure that you can use telnet to connect to the
server at the TCP port. Then try adding an alias for TCP/IP by using
cliconfg.exe at the client and try again.
Please feel free to let me know if you have any other questions or
concerns.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
|||Hi Dan,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
Charles Wang
Microsoft Online Community Support
sql