|
|
You're in the
sql server connect
group:3 server scenario problem
sql server connect:
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
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 [quoted text, click to view] "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
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |