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 execute sp/access sp properties using gui

I am not able to right click to execute a stored procedure or right click to access the stored procedure properties using Management Studio. Any ideas on what is causing this or if it is supposed to be this way?

Any help is much appreciated.

hi,

does the user you are in the database have enought permissions to do that?

regards

|||

Thanks for the response Andrea,

I login as sa.

Suppose I could mention this is on my local machine. Executing @.@.version returns:

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

I am the first one to be running on 2005 locally. Everyone else is still at 2000 so I have not been able to compare against other machines running express.

cannot execute sp/access sp properties using gui

I am not able to right click to execute a stored procedure or right click to access the stored procedure properties using Management Studio. Any ideas on what is causing this or if it is supposed to be this way?

Any help is much appreciated.

hi,

does the user you are in the database have enought permissions to do that?

regards

|||

Thanks for the response Andrea,

I login as sa.

Suppose I could mention this is on my local machine. Executing @.@.version returns:

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

I am the first one to be running on 2005 locally. Everyone else is still at 2000 so I have not been able to compare against other machines running express.

Cannot edit MS Access tables that are linked to SQL Server 2000

I am not able to edit tables through MS Access grid that is linked to sql
server 2000. I am able to edit some tables but not all.
Can you please tell me how I could change this so that I am able to edit all
the tables.
Thanks
> I am not able to edit tables through MS Access grid that is linked to sql
> server 2000. I am able to edit some tables but not all.
> Can you please tell me how I could change this so that I am able to edit
all
> the tables.
I guess you don't have Primary Key constraints on tables you cannot modify.
Check this first.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Cannot edit MS Access tables that are linked to SQL Server 2000

I am not able to edit tables through MS Access grid that is linked to sql
server 2000. I am able to edit some tables but not all.
Can you please tell me how I could change this so that I am able to edit all
the tables.
Thanks> I am not able to edit tables through MS Access grid that is linked to sql
> server 2000. I am able to edit some tables but not all.
> Can you please tell me how I could change this so that I am able to edit
all
> the tables.
I guess you don't have Primary Key constraints on tables you cannot modify.
Check this first.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Cannot edit MS Access tables that are linked to SQL Server 2000

I am not able to edit tables through MS Access grid that is linked to sql
server 2000. I am able to edit some tables but not all.
Can you please tell me how I could change this so that I am able to edit all
the tables.
Thanks> I am not able to edit tables through MS Access grid that is linked to sql
> server 2000. I am able to edit some tables but not all.
> Can you please tell me how I could change this so that I am able to edit
all
> the tables.
I guess you don't have Primary Key constraints on tables you cannot modify.
Check this first.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Cannot create linked server

I posted this in the Data Access forum already - may have been the wrong place...

I cannot create a linked to a MySql database in Enterprise Manager. Here is the relevant info:

Trying to create a linked server on a SQL Server 2000 Standard Edition Service Pack 4 running on a Windows 2000 Standard Edition server.

The MySQL server is running on Red Hat - not sure of the DB or OS version. However the DSN I created connects to the server correctly. I can connect using any of the MySQL tools.

I am using the latest ODBC MySQL drive 3.51...Here is the Provider string I am using...

DRIVER={MySQL ODBC 3.51 Driver} ;SERVER=192.168.1.99;DATABASE=bug_tracker; USER=UserID; PASSWORD=Password; OPTION=3

I have also tried using a System DSN to connect with, but that does not work either. It appears to create the linked server correctly each time, but I cannot access any of the tables. Here is the error message I get each time:

Error 7399: OLE DB Provider 'MSDASQL' reported an error. Datasource name not found and no default driver specified] OLD DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ]

Sure could use a little help!

Thanks!!!!

Brad Feaker

Please reply to forum only! Thanks!

Seems like no one else knows jack about this either!
|||

try this

Go to security/Linked Servers
Right Click on Linked Servers--> New Linked Server

Go to security/Linked Servers
Right Click on Linked Servers--> New Linked Server

Use Microsoft OLE DB Provider for ODBC Drivers

Use the Connection String
DRIVER={MySQL ODBC 3.51
Driver};SERVER=myserver.com;DATABASE=database;USER
=user;PASSWORD=password;OPTION=3

And in Provider Options select:

level zero only
Non-transacted updates (something)
Allow InProcess

let me know if you still get an error

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Still getting the same error :-( BTW - I bookmarked your blog. You have some good stuff there. Thanks for the effort. I will keep trying - Almost no experience with MySQL...

Brad
Ex Nihilo, Nihil Fit
|||

Do you have the MySQL Connector/ODBC 3.51 installed?

get it here-->http://dev.mysql.com/downloads/connector/odbc/3.51.html

also from a query window, does this work for you?

SELECT a.*
FROM OPENROWSET('MSDASQL',
'DRIVER={MySQL ODBC 3.51 Driver};SERVER=192.168.1.99;DATABASE=bug_tracker;user=UserID;PASSWORD=Password;OPTION=3',
TestDB ) AS a

change TestDB to your table name

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Yes - I have the latest driver installed - the OPENROWSET query give me the same error message as the linked server. I can open the MySQL Query Browser, connect and run queries with no problem. I just cannot seem to get a linked server to work with SQL Server 2000. This is really frustrating. If I can't get this set up, I am going to have to use DTS and write some real heavy-duty scripts as these tables are going to feed a data warehouse in Analysis Services. It also makes me feel pretty stupid because I cannot get it to work. Back to the drawing board and thanks for your help.

Brad
Ex Nihilo, Nihil Fit
|||

The driver is installed on the SQL server box right?

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Got it fixed finally...Thanks for everyone's help!
|||

I am having a similar problem with SSIS pulling data from MySQL

How did you get it fixed?

Thanks

-Raghu Raman

|||

Hi everybody, I have the same problem that has been treated here, but none of the solutions offered has been good for me.

Does anybody have any help respect to the problem with the linked server?

Thanks, blessings.

Cannot create linked server

I posted this in the Data Access forum already - may have been the wrong place...

I cannot create a linked to a MySql database in Enterprise Manager. Here is the relevant info:

Trying to create a linked server on a SQL Server 2000 Standard

Edition Service Pack 4 running on a Windows 2000 Standard Edition

server.

The MySQL server is running on Red Hat - not sure of the DB or OS

version. However the DSN I created connects to the server correctly.

I can connect using any of the MySQL tools.

I am using the latest ODBC MySQL drive 3.51...Here is the Provider string I am using...

DRIVER={MySQL ODBC 3.51 Driver} ;SERVER=192.168.1.99;DATABASE=bug_tracker; USER=UserID; PASSWORD=Password; OPTION=3

I have also tried using a System DSN to connect with, but that does

not work either. It appears to create the linked server correctly each

time, but I cannot access any of the tables. Here is the error

message I get each time:

Error 7399: OLE DB Provider 'MSDASQL' reported an error. Datasource

name not found and no default driver specified] OLD DB error trace

[OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned

0x80004005: ]

Sure could use a little help!

Thanks!!!!

Brad Feaker

Please reply to forum only! Thanks!

Seems like no one else knows jack about this either!|||

try this

Go to security/Linked Servers
Right Click on Linked Servers--> New Linked Server

Go to security/Linked Servers
Right Click on Linked Servers--> New Linked Server

Use Microsoft OLE DB Provider for ODBC Drivers

Use the Connection String
DRIVER={MySQL ODBC 3.51
Driver};SERVER=myserver.com;DATABASE=database;USER
=user;PASSWORD=password;OPTION=3

And in Provider Options select:

level zero only
Non-transacted updates (something)
Allow InProcess

let me know if you still get an error

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Still getting the same error :-( BTW - I bookmarked your blog. You have some good stuff there. Thanks for the effort. I will keep trying - Almost no experience with MySQL...

Brad
Ex Nihilo, Nihil Fit|||

Do you have the MySQL Connector/ODBC 3.51 installed?

get it here-->http://dev.mysql.com/downloads/connector/odbc/3.51.html

also from a query window, does this work for you?

SELECT a.*
FROM OPENROWSET('MSDASQL',
'DRIVER={MySQL ODBC 3.51 Driver};SERVER=192.168.1.99;DATABASE=bug_tracker;user=UserID;PASSWORD=Password;OPTION=3',
TestDB ) AS a

change TestDB to your table name

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Yes - I have the latest driver installed - the OPENROWSET query give me the same error message as the linked server. I can open the MySQL Query Browser, connect and run queries with no problem. I just cannot seem to get a linked server to work with SQL Server 2000. This is really frustrating. If I can't get this set up, I am going to have to use DTS and write some real heavy-duty scripts as these tables are going to feed a data warehouse in Analysis Services. It also makes me feel pretty stupid because I cannot get it to work. Back to the drawing board and thanks for your help.

Brad
Ex Nihilo, Nihil Fit|||

The driver is installed on the SQL server box right?

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Got it fixed finally...Thanks for everyone's help!|||

I am having a similar problem with SSIS pulling data from MySQL

How did you get it fixed?

Thanks

-Raghu Raman

|||

Hi everybody, I have the same problem that has been treated here, but none of the solutions offered has been good for me.

Does anybody have any help respect to the problem with the linked server?

Thanks, blessings.

Cannot create an ODBC connection to SQl 2000.

Hi all,

I have SQL 2000 enterprise edition(no service pack) installed on a Windows Server 2003 machine. I can remote access into the server and run SQL but I cannot create an ODBC connection to this from my desktop. From some research it looks like installing service packs might correct this but here is the error:

--connection failed

--sqlstate '01000'

--sql server error 10061

--connection failed

--sqlstate 08001

--sql server error 17

--SQL server does not exist or access denied

Any help would be appreciated.

Verify whether the SQL Server is listening on a TCP port. If it's a default instance it is by default 1433, so you can use

netstat -an | findstr 1433

If not but the TCP protocol is turned on for the server (and set to port 1433) then upgrading to the latest service pack will most likely solve your problem.

|||

It'd better for you to upgrade SQL Server 2000 to latest service pack 4 from here:

http://support.microsoft.com/kb/290211#EOADAA

then make sure following things before you make connection:

1) SQL Service is running

2) If make remote connection, make sure named pipe or TCP was enabled and try Peter's suggestion if you want to connect through tcp.

HTH.

cannot create a simple table

hello im brand new to SQL server but im very well versed in access so i know
how sql works, to aid me in my transition im the microsoft press SQLServer
2000 book and am trying to follow the simple instructions to create a table,
the script is very simple:
use northwind
create table investors
{
IvestorID int not null,
FirstName varchar(30) not null,
LastName varchar(30) not null
}
and ive made sure that ive copied it exactly but its still not working. IM
using the personal edition to get to grips with it before using the one at
work. Im logged in using windows authentication as the user Laptop/Dr patel
which is my username, and should have full database admin rights however i
get the following result when i run the above sql:
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
what am i doing wrong?use northwind
GO
create table investors
(
IvestorID int not null,
FirstName varchar(30) not null,
LastName varchar(30) not null
)
Your curly {} brackets were wrong.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"DowningDevelopments" <DowningDevelopments@.discussions.microsoft.com> wrote
in message news:90D63350-D145-4C98-9194-439CD14CC778@.microsoft.com...
> hello im brand new to SQL server but im very well versed in access so i
> know
> how sql works, to aid me in my transition im the microsoft press SQLServer
> 2000 book and am trying to follow the simple instructions to create a
> table,
> the script is very simple:
> use northwind
> create table investors
> {
> IvestorID int not null,
> FirstName varchar(30) not null,
> LastName varchar(30) not null
> }
> and ive made sure that ive copied it exactly but its still not working. IM
> using the personal edition to get to grips with it before using the one at
> work. Im logged in using windows authentication as the user Laptop/Dr
> patel
> which is my username, and should have full database admin rights however i
> get the following result when i run the above sql:
>
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violatio
n
> what am i doing wrong?
>|||Noooo, I figured it had to be something stupid like that!!!
thanks Mike
"Mike Epprecht (SQL MVP)" wrote:

> use northwind
> GO
> create table investors
> (
> IvestorID int not null,
> FirstName varchar(30) not null,
> LastName varchar(30) not null
> )
> Your curly {} brackets were wrong.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "DowningDevelopments" <DowningDevelopments@.discussions.microsoft.com> wrot
e
> in message news:90D63350-D145-4C98-9194-439CD14CC778@.microsoft.com...
>
>

Cannot Create a Relationship

I have three tables imported from Access that had relationships with
eachother and had Cascading Deletes set to TRUE. The tables related in a
simple manner.
tblPackingList.PackingListID to tblPackingListPart.PackingListID
tblPackingListPart.PackingListPartID to
tblPackingListPart.PackingListPartSerialID.
For some reason, I cannot establish the relationship between
tblPackingList.PackingListID and tblPackingListPart.PackingListID. I get the
following error.
tblPackingListPart' table
- Unable to create relationship 'FK_tblPackingListPart_tblPackingList'.
Introducing FOREIGN KEY constraint 'FK_tblPackingListPart_tblPackingList' on
table 'tblPackingListPart' may cause cycles or multiple cascade paths.
Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN
KEY constraints.
Could not create constraint. See previous errors.
What I did was delete the relationships from each table. The primary key for
each table is set to be Unique. The following keys are primary.
tblPackingList.PackingListID
tblPackingListPart.PackingListPartID
tblPackingListPartSerial.PackingListPartSerialID
I need the relationship between tblPackingList and tblPackingListPart to
Cascade Delete in the event the master record in tblPackingList is deleted,
but no matter what I do I cannot get he relationship to save. I keep getting
the above error no matter what I do. What must I do to resolve what appears
to me to be a bug. Or, am I missing something.
Thanks.
P.S. My apologies if this is posted under the wrong section, but I have no
idea what would be the appropriate area to post a question such as this.
Thanks.
Hello,
To understand the issue better, will you try to create three new tables
with similar schema to see if you could reproduce the issue. If so, will
you provide the exact script so that I may reproduce the issue on my side.
I tried the following script but I was not able to reproduce the problem.
=====
create table tblPackingList (PackingListID int primary key, packinglistname
varchar(20))
create table tblPackingListPart (PackingListPartID int primary key,
PackingListID int)
create table tblPackingListPartSerial (PackingListPartSerialID int primary
key, PackingListPartID int)
alter table tblpackingListPart add constraint fk_1 Foreign key
(PackingListID) references tblPackingList(PackingListID) on delete cascade
alter table tblpackingListPartSerial add constraint fk_2 Foreign key
(PackingListPartID) references tblPackingListPart(PackingListPartID) on
delete cascade
=====
Also, you may want to refer to the following article for more details:
321843Error message 1785 occurs when you create a FOREIGN KEY constraint
that may cause multiple cascade paths
http://support.microsoft.com/default.aspx?scid=kb;EN-US;321843
Please let's know if you have other related information. I'm looking
forward to your reply. Thank you!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Cannot Connect To SQL Server.

I'm relatively new to SQLServer. On one machine I have Access 2002
and the Developers edition of SQLServer 2000 and the Upsizing Wizard
works fine. On the other machine I have the same version of
SQLServer, but Access 2003. No matter what I have tried I can't get
the Wizard to connect to SQLServer. It tells me it can't find the
server or access denied. I have set SQL Server to mixed authentication
and have tried both domain and SQL login, and have failed both ways.
I don't think I'm doing anything different on this machine, with one
exception. I am using a Firewall (Norton) on the machine that fails.
I tried disabling it, but I still have the problem. I can link to SQL
Tables via ODBC so the server is working. It is only the Wizard that
is having problems (perhaps he was a Hogwarts dropout). Any help
would be appreciated.
Thanks.
Ira SolomonTry forcing a "shared memory" connection by putting "lpc:" before the name.
For example, if the instance name is MyServer\MyInstance, the servername of
lpc:MyServer\MyInstance will force it to connect with shared memory. If
it's a named instance you should also consider that the firewall may be
blocking UDP port 1434 which would mean you have to specify the port in the
connection string for TCP to work:
tcp:MyServer\MyInstance,1111 (if the sql error log shows that sql is
listening on tcp port 1111).
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Cindy:
Thanks.
'lpc:" works like a charm.
I knew about memory sharing but Books Online make no mention of lpc.
I just looked at some other online versions of books and I can't find it
there either.
This is a great help as I really need to use the large screen of the
desktop.
Ira
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Cannot connect to SQL server via NAT

Hi everyone.

We have a simple adsl router at our company and its provides the our user
with access... I looks like this

I
I
Adsl Router
I
------
I I
SQL 2k Srv ISA server

The router is configured to redirect all calls to port 1433 to the the ip
192.168.1.2 and other calls like 21 is redirected thru the ISA server to an
internal server.

I have no been able to connect(from my own home) to the databaseserver(the
instance), I only get a message like access denied etc(the sql user name etc
is ok).
But if I install web server on the same server which runs on port 80 and
redirect all incoming request(for port 80) to the SQL Server's webserver it
works without any problem...

Been struggling for a week now so I really need some help.

ThanksHello Holtis

Under CMD run svrnetcn.exe

Make sure you have protocol TCP/IP and Default Port 1433 under Properties.

Salvador|||Hi Salvador.

Thanks for your answer, I have tired that but it dosnt work anyway. Do I need to open any more ports in the firewall/router. I have also opened some additional UDP ports( I found a tip in the SQL manual) but nothings seems to help.

Seems like I have discoverd the unbeatable problem ;)

Best regards

cannot connect to sql server 2005 from xp client when trying to create ODBC dsn

Newbie here,

I am trying to link tables from an access 2003 frontend to sql server 2005 backend.

I am doing this in a vmware test environment. I am using vmware server and running sbs 2003 and xp sp2 client. Both virtaul machine can talk to each other(using local host connection).

I have tried to create a dsn to the sql server but i cannot connect. I can ping the sbs server through the command interface but the error i get when i try to connect is :

Connection failed:
Sql state: hyt00
sqlserver error: 0
microsoft odbc sql server driver timeout expired.

spent a day trying various combinations - still no joy.

Any help would be much appreciatedvirtual machines are nothing but trouble. open a query window and execute SELECT @.@.SERVERNAME|||Thank you for your reply,

I would agree vmware is not the best scenario - but as i am new to sql server - and i am about to upsize an access mdb backend to sql server 2005 so I thought some sort of test environment was required. I didn't fancy multiple installs on our production sbs until i got it right.

So from your post - is it possible to create a linked table between access 2003 and sql server 2005 from an access 2003 query window? If so, what would the syntax be if the sbs server was called 'sbs1' and the database on the sql server's default instance was named 'db1'? Or was your code to simply test the connectivity of the sql server?

The ultimate goal is a distributed access mde residing on xp sp2 clients created from an access mdb stored on a restricted network share that has linked tables to sql server 2005.

I do not even wish to go down the dsn road - would prefer the dsn-less road if possible - but i cannot proceed much further with that until i learn more about server logins, user id's and application roles etc and how passwords and user id's will be transferred accross network or stored on local machines. Hence I'm starting with dsn's and testing how to link the tables.

Any advice/guidance would be appreciated.|||I was eventually able to connect using named pipes - syntax had eluded me until i got lucky - server name 'sbs1' and pipe name was 'sbs1'.

So thats one hurdle - the next is which dsn settings to use - which server logins to use and which server roles to use.

The ultimate goal is a distributed access mde residing on xp sp2 clients created from an access mdb stored on a restricted network share that has linked tables to sql server 2005. Approx 4 users will have the abilty to link and import tables from sql server - all other users will be using an mde.

Any advice?

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.