Hi,
I am trying to connect to a SQL Server on a Windows2003 w/sp1 Server from a remote computer but always seem to get the infamous "Access is denied or Server Does Not Exist" error. However, if I try to connect from the machine that is running the server, there is no problem. (Used sql account, server=mixed authentication)
The windows 2003 Server is a single server installation with .Net 2005 Beta 2 Team Suite, Team Foundation Server and Sql Server - Installing these items took me a while, but eventually I managed to install from scatch to end without any installation errors.
I have triple checked that both TCP 1433 and UDP 1434 is open on my router and under windows firewall.
I have ensured that TCP/IP is enabled in SQL Server Configuration Manager for both "SQL Native Client Configuration" AND "Protocals for MSSQLServer". The default in the registry is DNETLIB (thus the appropriate protocal should be used based on the client)
I have set no Alias' in "SQL Native Client Configuration"
SQL Server is setup for mixed Authentication and I am trying to connect via a sql account.
SQL Server Browser is not running.
Below I have attached the results of running netstat -n, a sql log file and the results of a ipconfig /all
If there is any other information that one might need to help me figure out this problem, just ask.
Thanks for your help!
-Brian
Running netstat -n gives the following results
--
Proto Local Address Foreign Address State
TCP 127.0.0.1:135 127.0.0.1:4760 ESTABLISHED
TCP 127.0.0.1:389 127.0.0.1:1034 ESTABLISHED
TCP 127.0.0.1:389 127.0.0.1:1035 ESTABLISHED
TCP 127.0.0.1:389 127.0.0.1:1036 ESTABLISHED
TCP 127.0.0.1:389 127.0.0.1:1042 ESTABLISHED
TCP 127.0.0.1:1025 127.0.0.1:4761 ESTABLISHED
TCP 127.0.0.1:1034 127.0.0.1:389 ESTABLISHED
TCP 127.0.0.1:1035 127.0.0.1:389 ESTABLISHED
TCP 127.0.0.1:1036 127.0.0.1:389 ESTABLISHED
TCP 127.0.0.1:1042 127.0.0.1:389 ESTABLISHED
TCP 127.0.0.1:4760 127.0.0.1:135 ESTABLISHED
TCP 127.0.0.1:4761 127.0.0.1:1025 ESTABLISHED
TCP 192.168.0.164:389 192.168.0.164:1145 ESTABLISHED
TCP 192.168.0.164:1025 192.168.0.164:1147 ESTABLISHED
TCP 192.168.0.164:1025 192.168.0.164:1640 ESTABLISHED
TCP 192.168.0.164:1145 192.168.0.164:389 ESTABLISHED
TCP 192.168.0.164:1147 192.168.0.164:1025 ESTABLISHED
TCP 192.168.0.164:1150 192.168.0.164:8080 ESTABLISHED
TCP 192.168.0.164:1433 192.168.0.164:3705 ESTABLISHED
TCP 192.168.0.164:1433 192.168.0.164:3706 ESTABLISHED
TCP 192.168.0.164:1433 192.168.0.164:3707 ESTABLISHED
TCP 192.168.0.164:1433 192.168.0.164:3708 ESTABLISHED
TCP 192.168.0.164:1433 192.168.0.164:3720 ESTABLISHED
TCP 192.168.0.164:1433 192.168.0.164:3811 ESTABLISHED
TCP 192.168.0.164:1433 192.168.0.164:6187 ESTABLISHED
TCP 192.168.0.164:1433 192.168.0.164:6188 ESTABLISHED
TCP 192.168.0.164:1640 192.168.0.164:1025 ESTABLISHED
TCP 192.168.0.164:3187 192.168.0.164:8080 ESTABLISHED
TCP 192.168.0.164:3705 192.168.0.164:1433 ESTABLISHED
TCP 192.168.0.164:3706 192.168.0.164:1433 ESTABLISHED
TCP 192.168.0.164:3707 192.168.0.164:1433 ESTABLISHED
TCP 192.168.0.164:3708 192.168.0.164:1433 ESTABLISHED
TCP 192.168.0.164:3720 192.168.0.164:1433 ESTABLISHED
TCP 192.168.0.164:3811 192.168.0.164:1433 ESTABLISHED
TCP 192.168.0.164:5891 192.168.0.164:8080 ESTABLISHED
TCP 192.168.0.164:5896 192.168.0.164:8080 ESTABLISHED
TCP 192.168.0.164:6157 192.168.0.164:8080 TIME_WAIT
TCP 192.168.0.164:6158 192.168.0.164:8080 TIME_WAIT
TCP 192.168.0.164:6174 192.168.0.164:2383 TIME_WAIT
TCP 192.168.0.164:6187 192.168.0.164:1433 ESTABLISHED
TCP 192.168.0.164:6188 192.168.0.164:1433 ESTABLISHED
TCP 192.168.0.164:8080 192.168.0.164:1150 ESTABLISHED
TCP 192.168.0.164:8080 192.168.0.164:3187 ESTABLISHED
TCP 192.168.0.164:8080 192.168.0.164:5891 ESTABLISHED
TCP 192.168.0.164:8080 192.168.0.164:5896 ESTABLISHED
The following is one of my sql Logs.
Date,Source,Severity,Message
06/17/2005 07:24:14,spid20s,Unknown,Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.
06/17/2005 07:19:45,spid53,Unknown,Using 'xpstar90.dll' version '2000.90.1116' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
06/17/2005 07:19:45,spid53,Unknown,Using 'xpsqlbot.dll' version '2000.90.1116' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
06/17/2005 07:19:40,spid11s,Unknown,Service Broker manager has started.
06/17/2005 07:19:40,spid11s,Unknown,The Database Mirroring protocol transport is disabled or not configured.
06/17/2005 07:19:40,spid11s,Unknown,The Service Broker protocol transport is disabled or not configured.
06/17/2005 07:19:40,spid4s,Unknown,Recovery is complete. This is an informational message only. No user action is required.
06/17/2005 07:19:38,spid8s,Unknown,Starting up database 'tempdb'.
06/17/2005 07:19:33,spid13s,Unknown,Starting up database 'rlbss'.
06/17/2005 07:19:32,spid11s,Unknown,Starting up database 'BisDWDB'.
06/17/2005 07:19:32,spid4s,Unknown,Recovery is writing a checkpoint in database 'BisDB' (8). This is an informational message only. No user action is required.
06/17/2005 07:19:26,spid14s,Unknown,Starting up database 'VSTEAMTeamBuild'.
06/17/2005 07:19:23,spid13s,Unknown,Starting up database 'VSTEAMSCCAdmin'.
06/17/2005 07:19:23,spid11s,Unknown,Starting up database 'VSTEAMSCC'.
06/17/2005 07:19:22,spid12s,Unknown,Starting up database 'BisDB'.
06/17/2005 07:19:22,spid12s,Unknown,Recovery is writing a checkpoint in database 'CurrituckDB' (7). This is an informational message only. No user action is required.
06/17/2005 07:19:22,spid8s,Unknown,Clearing tempdb database.
06/17/2005 07:19:20,Logon,Unknown,Login failed for user 'NT AUTHORITY\SYSTEM'. [CLIENT: 192.168.0.164]
06/17/2005 07:19:20,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 1.
06/17/2005 07:19:17,spid14s,Unknown,Starting up database 'CurrituckDB'.
06/17/2005 07:19:17,spid13s,Unknown,Starting up database 'ReportServerTempDB'.
06/17/2005 07:19:17,spid12s,Unknown,Starting up database 'ReportServer'.
06/17/2005 07:19:16,spid11s,Unknown,Starting up database 'msdb'.
06/17/2005 07:19:16,Server,Unknown,SQL Server is now ready for client connections. This is an informational message; no user action is required.
06/17/2005 07:19:16,Server,Unknown,The SQL Network Interface library was unable to register SPN. Error: 0x54b.
06/17/2005 07:19:16,Server,Unknown,Error: 26037<c/> Severity: 16<c/> State: 1.
06/17/2005 07:19:16,Server,Unknown,Dedicated admin connection support was established for listening locally on port 1434.
06/17/2005 07:19:16,Server,Unknown,Server is listening on [ 127.0.0.1 <ipv4> 1434].
06/17/2005 07:19:16,Server,Unknown,Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ].
06/17/2005 07:19:16,Server,Unknown,Server shared memory provider is ready for clients to connect to [ \\.\pipe\SQLSm\MSSQLSERVER ].
06/17/2005 07:19:16,Server,Unknown,Server is listening on [ 'any' <ipv4> 1433].
06/17/2005 07:19:16,spid8s,Unknown,Starting up database 'model'.
06/17/2005 07:19:16,Server,Unknown,A self-generated certificate was successfully loaded for encryption.
06/17/2005 07:19:16,spid4s,Unknown,Server name is 'BRIAN-SRV'. This is an informational message only. No user action is required.
06/17/2005 07:19:14,spid4s,Unknown,Starting up database 'mssqlsystemresource'.
06/17/2005 07:19:14,spid4s,Unknown,SQL Trace ID 1 was started by login "sa".
06/17/2005 07:19:14,spid4s,Unknown,Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
06/17/2005 07:19:12,spid4s,Unknown,Starting up database 'master'.
06/17/2005 07:19:12,Server,Unknown,Database mirroring has been enabled on this instance of SQL Server.
06/17/2005 07:19:12,Server,Unknown,Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
06/17/2005 07:19:11,Server,Unknown,Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
06/17/2005 07:19:11,Server,Unknown,Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.
06/17/2005 07:19:10,Server,Unknown,Detected 1 CPUs. This is an informational message; no user action is required.
06/17/2005 07:19:10,Server,Unknown,SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
06/17/2005 07:19:10,Server,Unknown,-l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
06/17/2005 07:19:10,Server,Unknown,-e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
06/17/2005 07:19:10,Server,Unknown,-d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
06/17/2005 07:19:10,Server,Unknown,Registry startup parameters:
06/17/2005 07:19:10,Server,Unknown,This instance of SQL Server last reported using a process ID of 640 at 6/17/2005 3:23:15 AM (local) 6/17/2005 7:23:15 AM (UTC). This is an informational message only; no user action is required.
06/17/2005 07:19:10,Server,Unknown,Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
06/17/2005 07:19:10,Server,Unknown,Server process ID is 736.
06/17/2005 07:19:10,Server,Unknown,All rights reserved.
06/17/2005 07:19:10,Server,Unknown,(c) 2004 Microsoft Corporation.
06/17/2005 07:19:10,Server,Unknown,Microsoft SQL Server 2005 - 9.00.1116 (Intel X86) Apr 9 2005 20:56:37 Copyright (c) 1988-2004 Microsoft Corporation Beta Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
results of IPCONFIG /all
Windows IP Configuration
Host Name . . . . . . . . . . . . : brian-srv
Primary Dns Suffix . . . . . . . : brianfurner.local
Node Type . . . . . . . . . . . . : Unknown
IP Routing Enabled. . . . . . . . : No
WINS Proxy Enabled. . . . . . . . : No
DNS Suffix Search List. . . . . . : brianfurner.local
cityxx.phub.net.cable.rogers.com
Ethernet adapter Local Area Connection:
Connection-specific DNS Suffix . : cityxx.phub.net.cable.rogers.com
Description . . . . . . . . . . . : SiS 900-Based PCI Fast Ethernet Adapter
Physical Address. . . . . . . . . : 00-0B-6A-2E-44-A4
DHCP Enabled. . . . . . . . . . . : Yes
Autoconfiguration Enabled . . . . : Yes
IP Address. . . . . . . . . . . . : 192.168.0.164
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 192.168.0.1
DHCP Server . . . . . . . . . . . : 192.168.0.1
DNS Servers . . . . . . . . . . . : 127.0.0.1
192.168.0.164
Lease Obtained. . . . . . . . . . : Friday, June 17, 2005 7:18:35 AM
Lease Expires . . . . . . . . . . : Friday, June 24, 2005 7:18:35 AM
Also, if you wish to see a list of the services running on my server, please click this link.
http://hoser.hopto.org:97/test.htm
Thanks
|||I am still trying to get this working, I am pleading, if someone has any ideas, please let me know.
Thanks
|||Is there a reason you chose not to start SQL Browser?What is the instance name and what are you typing in the connection string of the app.
-Euan|||
Hi,
I did not start SQL Browser, as I read it is not necessary so long as you specifically set the port in TCP/IP settings in Configuration Manager.
I believe the instance name as the same as the computer name. When I hold my mouse over the sql server icon in the taskbar it shows this
BRIAN-SRV - MSSQLServer
The connection string is irrelevant at this point. I was trying to connect using VS.NET 2005 Server explorer. I chose to "Add a Data Connection"
Entered my IP Address as my server name.
my SQL user account and Password and my initial catalog.
THanks.
|||The instance name may be the same as the computer name. Did you install as a named instance or default instance?You can tell this by looking in the registry for the SQL Server services, if they have something in () after the service name then you have a named instance.
In that case you must run SQL Browser for port resolution or you must fix the port number of the instance(by default named instance ports are dynamic, on the default instance is statically assigned and by default this is 1433).|||Another thought, does the server have a valid IP address, at one point in the log it is listening on 127.0.0.1, thats the loopback connection.
Can you connect locally using sqlcmd?
-Euan|||Hey Euan,
Thanks for your help.
Yes, I can connect locally using sqlcmd.
IN
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server
I see a key named InstalledInstances. ITs value is MSSQLSERVER SHAREPOINT
I had also wondered if the server was only loopingback. But I do not know how to change this within SQL Server.
Thanks Euan.|||Hey Euan,
Here is my ip configuration for SQL SERVER.
http://hoser.hopto.org:97/ip1.jpg
http://hoser.hopto.org:97/ip2.jpg
http://hoser.hopto.org:97/ipall.jpg
Thanks again!
-Brian|||Try going to www.grc.com and using shields up to test port 1433 to see if your firewall is open on that port and forwarding it to SQL server.|||Thanks Paul!
Through the help of everyone, Euan, Paul and some other friends, we discovered the problem was much more simple then meets the eye.
I should have guessed because I am perfect that it would not be a problem with my settings :P (joke)
Rogers High Speed Internet ISP blocks port 1433 because it is a common port for virus attacks.
I set my router to redirect external traffic on a different port to 1433 internally and voila, successful connection.
I hope this post will help others.
THanks again for everyones help!
-Brian|||For those still grappling with this problem, I needed to run SQL Server Configuration Manager and expand the node "SQL Server 2005 Network Configuration/Protocols for <instance name>". I then enabled the TCP/IP protocol and voila! I was able to connect to the 2005 SQL Server using a remote device.|||
I have just installed the ssme ctp on the machine I am running vs2005c#Express and it cant find the connection either. I am not accessing it remotely, although I would like to be able to in the future, but for now I would just like to do all my work on this same machine. Any ideas why I can not get a connection?
Roger
|||I had the same problem, and those jerks at rogers swore up and downthey weren't blocking port 1433, but after changing the port, mine
worked too!
I'm so pissed I'd like to complain to one of their vp's, but that old
saying about &%#^ rolling downhill was probably started at in the
context of rogers.|||
Thank you so much! I had the exact same problem as you, and I was about to tear my hair off, but now I changed the port and it all works like it should.Brian Furner wrote:
Thanks Paul! Through the help of everyone, Euan, Paul and some other friends, we discovered the problem was much more simple then meets the eye.
I should have guessed because I am perfect that it would not be a problem with my settings :P (joke)
Rogers High Speed Internet ISP blocks port 1433 because it is a common port for virus attacks.
I set my router to redirect external traffic on a different port to 1433 internally and voila, successful connection.
I hope this post will help others.
THanks again for everyones help!
-Brian
Indeed your post helps others, and I marked it as such :)
Again, thank you.
No comments:
Post a Comment