cannot execute xp_cmdshell. Access is denied while copying data over the network

Hi,

I am having some trouble copying data over my workgroup network from my Windows 2003 Server Machine (machineA with SQL SERVER 2005) to one of my network Machine's drive(MachineB).
Here is the T-SQL code that I am trying to execute:

EXEC xp_cmdshell 'copy D:\Data\file.txt \\MachineB\Documents'

Whenever I tried to execute the above piece of code, I get the error message "Access is denied", but if I try to copy the file from the Command Prompt (cmd.exe) with the copy command, the file copies fine over the network.

I have already searched over the internet and I found out that loads of people have the same issue, and they were suggested something like this:
"Check in Services and make sure that the MSSQLServer service is run as a domain user and that domain user has rights to these network resources."

Well it sounds plausible, but I don't know what are the exact steps to do this. How do I know which user is running the MSSQL Server service? Are they referring to the user which I use to connect to my SQL Server Database engine throuhg the SQL Server Management Studio?
Also they are suggesting 'domain user', and as I said before I do not have domain network just regular simple workgroup network.

Here are some details of the user that I use to login. I generally login into my Windows 2003 Server machine with user called 'User1' and I use the same 'User1' to connect to SQL Server through the Management Studio Screen.
Should I create a user called 'User1' on my MachineB(Destination Machine)?

I would really appreciate, if someone can give me detailed steps explaning how to solve this problem.

Thank you very much once again.

Open the Surface Area Configuration Manager. Go to the features section. Make sure that xp_cmdshell is enabled. (It is turned off by default.) Then once you have enabled xp_cmdshell, make sure that the SQL Server service account has the appropriate permissions granted.|||Hi Michael,

Thank you very much for your reply. xp_cmdshell is already enabled. How do I check the permissions of SQL Server Srvice account? Is that the same account which I use to connect to SQL SERVER through Management Studio?

Thanks

|||No. It is the Windows account that the SQL Server service is running under. Specifically, you can find this within SQL Server Configuration Manager.|||

Michael Hotek wrote:

No. It is the Windows account that the SQL Server service is running under. Specifically, you can find this within SQL Server Configuration Manager.

Ok I went to the SQL SERVER Configuration Manager. Then I clicked on SQL SERVER 2005 Services in the left pane. On the right pane, it listen 5-6 services running like Intergration Service, Analysis Service, Reporting Service, and so on. One of them was SQL SERVER(MSSQLSERVER), so I think this must be the one that you were talking about. I right clicked on it-> Properties. Under the properties tab, I saw Log on as: Built-in Account (radio button) was checked and in the dropdown below it, Local System was selected. So basically the SQL Server Service is running under Local System. Now how do I figure out what user is associated with Local System?

Thank you once again for the help.Much appreciated.

|||Local system is the local machine account. That would be why you are getting an access denied on a network resource. The SID for localsystem is not valid external to the machine. In order to be able to execute that command you would need to create a Windows login and then use that login to run the SQL Server services. That login would have to have permissions on the network resource you are trying to write to.|||Thanks a lot Michael, that really makes sense .

Now for the extension based on your reply. Are there any disadvantages of using a Windows user to run the SQL Server services instead of a Local Machine account?

Thank you very very much for your prompt reply and insightful suggestion.

|||No, in fact, there are entire feature sets that you can't use in SQL Server if you are running under the localsystem account, replication being one of them. I don't run any SQL Server under a local machine account. I always run them under specific user accounts and in particular regular domain user accounts.|||I just did this and it worked like a charm. You someone already posted the answer. Go to SQL Server Configuration Manager. Right click on Sql Server, click properties and change the user account to run under a domain or local pc account. that has access to the share.

cannot execute xp_cmdshell. Access is denied while copying data over the network

Hi,

I am having some trouble copying data over my workgroup network from my Windows 2003 Server Machine (machineA with SQL SERVER 2005) to one of my network Machine's drive(MachineB).
Here is the T-SQL code that I am trying to execute:

EXEC xp_cmdshell 'copy D:\Data\file.txt \\MachineB\Documents'

Whenever I tried to execute the above piece of code, I get the error message "Access is denied", but if I try to copy the file from the Command Prompt (cmd.exe) with the copy command, the file copies fine over the network.

I have already searched over the internet and I found out that loads of people have the same issue, and they were suggested something like this:
"Check in Services and make sure that the MSSQLServer service is run as

a domain user and that domain user has rights to these network

resources."

Well it sounds plausible, but I don't know what are the exact steps to do this. How do I know which user is running the MSSQL Server service? Are they referring to the user which I use to connect to my SQL Server Database engine throuhg the SQL Server Management Studio?
Also they are suggesting 'domain user', and as I said before I do not have domain network just regular simple workgroup network.

Here are some details of the user that I use to login. I generally login into my Windows 2003 Server machine with user called 'User1' and I use the same 'User1' to connect to SQL Server through the Management Studio Screen.
Should I create a user called 'User1' on my MachineB(Destination Machine)?

I would really appreciate, if someone can give me detailed steps explaning how to solve this problem.

Thank you very much once again.Open the Surface Area Configuration Manager. Go to the features section. Make sure that xp_cmdshell is enabled. (It is turned off by default.) Then once you have enabled xp_cmdshell, make sure that the SQL Server service account has the appropriate permissions granted.|||Hi Michael,

Thank you very much for your reply. xp_cmdshell is already enabled. How do I check the permissions of SQL Server Srvice account? Is that the same account which I use to connect to SQL SERVER through Management Studio?

Thanks|||No. It is the Windows account that the SQL Server service is running under. Specifically, you can find this within SQL Server Configuration Manager.|||

Michael Hotek wrote:

No. It is the Windows account that the SQL Server service is running under. Specifically, you can find this within SQL Server Configuration Manager.

Ok I went to the SQL SERVER Configuration Manager. Then I clicked on SQL SERVER 2005 Services in the left pane. On the right pane, it listen 5-6 services running like Intergration Service, Analysis Service, Reporting Service, and so on. One of them was SQL SERVER(MSSQLSERVER), so I think this must be the one that you were talking about. I right clicked on it-> Properties. Under the properties tab, I saw Log on as: Built-in Account (radio button) was checked and in the dropdown below it, Local System was selected. So basically the SQL Server Service is running under Local System. Now how do I figure out what user is associated with Local System?

Thank you once again for the help.Much appreciated.|||Local system is the local machine account. That would be why you are getting an access denied on a network resource. The SID for localsystem is not valid external to the machine. In order to be able to execute that command you would need to create a Windows login and then use that login to run the SQL Server services. That login would have to have permissions on the network resource you are trying to write to.|||Thanks a lot Michael, that really makes sense .

Now for the extension based on your reply. Are there any disadvantages of using a Windows user to run the SQL Server services instead of a Local Machine account?

Thank you very very much for your prompt reply and insightful suggestion.|||No, in fact, there are entire feature sets that you can't use in SQL Server if you are running under the localsystem account, replication being one of them. I don't run any SQL Server under a local machine account. I always run them under specific user accounts and in particular regular domain user accounts.|||I just did this and it worked like a charm. You someone already posted the answer. Go to SQL Server Configuration Manager. Right click on Sql Server, click properties and change the user account to run under a domain or local pc account. that has access to the share.

cannot execute xp_cmdshell. Access is denied while copying data over the network

Hi,

I am havingsome trouble copying data over my workgroup network from my Windows2003 Server Machine (machineA with SQL SERVER 2005) to one of mynetwork Machine's drive(MachineB).
Here is the T-SQL code that I am trying to execute:

EXEC xp_cmdshell 'copy D:\Data\file.txt \\MachineB\Documents'

WheneverI tried to execute the above piece of code, I get the error message"Access is denied", but if I try to copy the file from the CommandPrompt (cmd.exe) with the copy command, the file copies fine over thenetwork.

I have already searched over the internet and I foundout that loads of people have the same issue, and they were suggestedsomething like this:
"Check in Services and make sure that the MSSQLServer service is run asa domain user and that domain user has rights to these networkresources."

Wellit sounds plausible, but I don't know what are the exact steps to dothis. How do I know which user is running the MSSQL Server service? Arethey referring to the user which I use to connect to my SQL ServerDatabase engine throuhg the SQL Server Management Studio?
Also they are suggesting 'domain user', and as I said before I do not have domain network just regular simple workgroup network.

Hereare some details of the user that I use to login. I generally logininto my Windows 2003 Server machine with user called 'User1' and I usethe same 'User1' to connect to SQL Server through the Management StudioScreen.
Should I create a user called 'User1' on my MachineB(Destination Machine)?

I would really appreciate, if someone can give me detailed steps explaning how to solve this problem.

Thank you very much once again.Check out the SQL Server Surface Area Configuration under SQL Server 2005 configuration. xp_cmdshell is disabled by default. You need to enable it manually and give permissions.|||

ndinakar:

Check out the SQL Server Surface Area Configuration under SQL Server 2005 configuration. xp_cmdshell is disabled by default. You need to enable it manually and give permissions.

Thank you very much for your reply. xp_cmdshell is already enabled.

|||

modest:

"Check in Services and make sure that the MSSQLServer service is run as a domain user and that domain user has rights to these network resources."

Well it sounds plausible, but I don't know what are the exact steps to do this.

You're on the rigth way goto Start->All Programs->Microsoft SQL Server2005-> Configuration Tools->SQL Server Configuration Manager->SQL Server 2005 Services->locate the service for the SQL2005 instance, should be "SQL Server (InstanceName)"->double click it-> on the Log On tab, you can see the start up account for the service. Just make sure this account has proper permission on the remote resource.

|||Yeah I did that and it says under buit-in account, the user is Local System. Now how do I find out which user is associated with Local System.

Thanks for the reply.|||

modest:

Yeah I did that and it says under buit-in account, the user is Local System. Now how do I find out which user is associated with Local System.

Thanks for the reply.

xp_cmdshell runs with SQL Server Agent permissions Microsoft have finally documented the permission requirements. Try the link below and follow the instructions. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms175046.aspx

|||

Caddre:

modest:

Yeah I did that and it says under buit-in account, the user is Local System. Now how do I find out which user is associated with Local System.

Thanks for the reply.

xp_cmdshell runs with SQL Server Agent permissions Microsoft have finally documented the permission requirements. Try the link below and follow the instructions. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms175046.aspx

Well I already have the proxy credential created for the User1 Windows account as I use that credential to execute the dtsRun command from the xp_cmdshell and that runs fine. The problem is when I am trying to copy something over the network. For example I can copy a file within my local machine by using the xp_cmdshell stored procedure, but it won;t work if the destination drive is a network drive.|||That is because you installed SQL Server with the local systems account when you do that and most developers do, SQL Server service and SQL Server Agent are without access to network resources. Try running replication service and you will see what I am talking about. Run a search for local systems account limitations in SQL Server BOL (books online). Hope this helps.|||

Caddre:

That is because you installed SQL Server with the local systems account when you do that and most developers do, SQL Server service and SQL Server Agent are without access to network resources. Try running replication service and you will see what I am talking about. Run a search for local systems account limitations in SQL Server BOL (books online). Hope this helps.

Thanks. Actually I was worried, if changing the startup user for SQLSERVER 2005 will affect my ASP/ASP.NET scripts or for that matter IIS6.0. Will this change be transparent to all the users on the machineand the other machine on my network which connect to the SQL SERVER onmy machine?|||It is not complicated just run full backup of all your databases and rerun setup and make sure you install SQL Server service and SQL Server Agent with two Domain accounts with network permissions. And no it will not affect your application if you do the above because if you run into permissions problem your backup will help if recreate the databases before the changes. But I don't think it will affect anything because you are just giving SQL Server service and the Agent network access. Hope this helps.|||

Caddre:

It is not complicated just run full backup of all your databases and rerun setup and make sure you install SQL Server service and SQL Server Agent with two Domain accounts with network permissions. And no it will not affect your application if you do the above because if you run into permissions problem your backup will help if recreate the databases before the changes. But I don't think it will affect anything because you are just giving SQL Server service and the Agent network access. Hope this helps.

Thanks Caddre very much. I just have one question based on your suggestion. What do you mean by domain accout? Do you mean like actual Domain Network on which my computer is connected? As I said in my first post, we have a workgroup network and not domain network. I know I am confusing with the word Domain. Does domain account mean, a new Windows user that I create on my machine? Or does it mean something lese?

Thank you once again.|||OK my bad create service accounts in your workgroup to run SQL Server service and SQL Server Agent and no you cannot run both on one account because the Agent can do a lot of automation in your application for you so it is good practice to run them on separate accounts. Hope this helps.

Cannot connect to SQL Server 2005 April CTP from remote computer

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 down

they 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.|||

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

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.
Indeed your post helps others, and I marked it as such :)

Again, thank you.

Cannot connect to SQL Server 2005 April CTP from remote computer

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 down

they 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.|||

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

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.
Indeed your post helps others, and I marked it as such :)

Again, thank you.

Cannot connect to SQL Server 2005 April CTP from remote computer

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 down

they 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.|||

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

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.
Indeed your post helps others, and I marked it as such :)

Again, thank you.

Cannot connect to SQL Server 2005 April CTP from remote computer

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 down

they 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.|||

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

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.
Indeed your post helps others, and I marked it as such :)

Again, thank you.

Cannot connect to SQL Server 2005 April CTP from remote computer

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 down they 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.
|||

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

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.
Indeed your post helps others, and I marked it as such :)

Again, thank you.