Connecting to SQL Server 2000 Named Instances

I have the following problem;

I fully understand that to connect to a named instance of SQL Server you need to use the ServerName\SQLInstanceName. The problem I have is that I have a SQL Server in a different zone. I can connect to the Default instance by IP Address or the ServerName.zone.domain.org. (e.g. MySQLServer.zone1.mydomain.org).

However, the same thing does not work for the Named Instance. It seems to be named instance or nothing.

How can I connect to this named instance across network zones?:Suse the instance name with the ip address, like 1.2.3.4\myInstance and you ought to be fine.

-PatP|||That did it. Thanks!!! :beer:

Connecting to SQL server

Select * from ServerName.DatabaseName.OwnerOrSchema.Objectname

I tyied the way given above.But when im going to run it on query analizer it gives an error massege saying that

"Server 'JOY' is not configured for DATA ACCESS.'

How can i configure the particuler sever.

Thanks

Set the server configuration to allow remote connections.

Connecting SQL2000 problem

Hi All,

I have a sqlserver 2000 & 2005 both installed on same server with the instance name as servername\sql2000 and servername\sql2005.

When i try to connect the sql2000 database from dotnet2002 or 2003, It comes up with an error as sql server doesnot exist or access denied but when i tried with dotnet2005, i am able to connect it.

I guess it has to do with an dotnet framework 1.0 & 1.1 but i am not sure where to look for it.

Can someone please point me to right direction ?

Thanks

Shaik Nagul

on the client, check that the TCP protocols are enabled using the Client Network Utility (cliconfig.exe) and on the server there is the analog Server Network Utility (svrnetcn.exe)|||

Hi,

Thanks for the reply.

I have checked on both client and server, the tcp/ip protocol is enabled and the default port is set to 1433.

But I am still unable to connect to sqlserver.

Anymore any ideas.

Thanks

|||

for a sanity check. . .

look at services control panel applet connected to the server.

does it say:

MSSQL$SQL2000

or

MSSQLSERVER$SQL2000

?

|||

I need to look closer at these posts - I bet it does say one of the above.

ok, are you using SQLClient in 2003? or OleDB?

what does your connection string look like for the 2003 connection?

SQL Authentication or SSPI?

|||

i am using sqlclient to access the database from both 2002 and 2003. My connection string is as

objCon.ConnectionString = "server=devserver\sql2000;database=northwind;uid=sa;pwd=;"

and in the services it does say

MSSQL$SQL2000

Thanks

|||

first compare that connection string to the other connection string.

isnt it supposed to look like:

Data Source=devserver\sql2000;Initial Catalog=northwind;Password=12abc12;User ID=sa;

are you running sql server authentication?

I bet there is an sa password you need (especially if this is a sp3a or above)

Do you have a windows domain? If you do, add your windows account to SQL Server as a db admin (Or make yourself a local administrator on the machine) and use the following:

Integrated Security=SSPI;Initial Catalog=northwind;Data Source=devserver\sql2000;

|||

Please refer to

http://blogs.msdn.com/sql%5Fprotocols/

There are several very useful articles for connectivity trouble-shooting.

It is really bizarre that you can connect using net2.0, but not net1.0. Since you can connect using net2.0 with the same connection string, the problem should be on the client side. To isolate the problem,

0. Is this remote connection or local connection?

1. please verify which connection provider is used by net2.0 by issuing following query after connected, "select net_library from sysprocesses where spid=@.@.spid". I would expect it is either TCP( remote ) or LPC (local).

2. Either case, I would like you to try more explicit connection string by replacing "devserver\sql2000" with "tcp:devserver:tcpport", and try connectioin using dotnet1.0.

You should be able to find the tcp port number that ss2k is listening in either server ERRORLOG or server side registry. According to your post, I assume TCP is enabled on both server and client, especially dotnet1.0 client.

for example, in server ERRORLOG

2006-01-17 11:54:53.86 server SQL server listening on XXXXXXXX: 1433.
2006-01-17 11:54:53.89 server SQL server listening on XXXXXXXX: 1433.

The registry key of TCP port should be located under

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\sql2000\MSSQLServer\SuperSocketNetLib\Tcp.

If you can connect, then there is an issue translating connection string. We need to take further action to diagnose the issue.

Please post your test results.

|||

As far as i think, the problem exist on the server because from the same machine i am able to connect to another sqlserver which doesn't have sql2005.

Thanks guys for trying to help but i have formatted the machine and reinstall the sqlserver2000 and now everything works fine. I would have not done this if there is no time constraint but since we are working on deadlines i have to do that.

Connect via ServerName but not by IP or FQDN

How can I make it so that our intranet clients can connect to the SQLServer
via our FQDN? Our extranet clients work fine via that way, but once they go
behind the VPN, the FQDN doesn't work. Also for onsite intranet clients we
can connect fine via server name, but not via IP address or FQDN.
(connecting via the ODBC SQL Server Driver in the Data Source Administrator)
Hi Brett,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to connect to your SQL
Server with FQDN name via VPN. If I have misunderstood your concern, please
feel free to point it out.
Based on my knowledge, if you connect via VPN, your client will be on the
intranet and please make sure your intranet DNS server could resolve that
FQDN. What do your intranet clients used to connect the SQL Server? server
name, ip address or FQDN name?
Please connect via VPN and perform the test below
1. PING with FQDN name and IP address (make sure you are using it's
intranet IP address)
2. telnet <FQDN name or IP address> <Port number, usually 1433>
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||I'd like my intranet clients (and extranet clients) to be able to connect
using the FQDN -- but you're correct they cannot resolve that name right
now on the intranet (or if behind the VPN). Why would that be? They can
ping the www.mydomain.com, but if I use www.mydomain.com as the server name,
it isn't resolved. How can I get it to resolve the FQDN for a SQL Server
ODBC connection?
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:7H$Voa50FHA.3328@.TK2MSFTNGXA01.phx.gbl...
> Hi Brett,
> Welcome to use MSDN Managed Newsgroup!
> From your descriptions, I understood you would like to connect to your SQL
> Server with FQDN name via VPN. If I have misunderstood your concern,
> please
> feel free to point it out.
> Based on my knowledge, if you connect via VPN, your client will be on the
> intranet and please make sure your intranet DNS server could resolve that
> FQDN. What do your intranet clients used to connect the SQL Server? server
> name, ip address or FQDN name?
> Please connect via VPN and perform the test below
> 1. PING with FQDN name and IP address (make sure you are using it's
> intranet IP address)
> 2. telnet <FQDN name or IP address> <Port number, usually 1433>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
|||Hi Brett,
It seems none of your intranet clients (no matter it conncet directly in
the intranet or connnect via VPN) will be able to connet the SQL Server by
FQDN name. If I have misunderstood your concern, please feel free to point
it out.
Please perform the steps below and let me know the results
1. check whether there are any Firewall settings in your intranet to see
whether it blocks the connection.
2. perform the command below and let me know the result
TELNET <FQDN name or IP address> <Port number, usually 1433>
3. What's the IP address return back from ping www.mydomain.com. Does it
match the IP address of your SQL Server?
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Connect via ServerName but not by IP or FQDN

How can I make it so that our intranet clients can connect to the SQLServer
via our FQDN? Our extranet clients work fine via that way, but once they go
behind the VPN, the FQDN doesn't work. Also for onsite intranet clients we
can connect fine via server name, but not via IP address or FQDN.
(connecting via the ODBC SQL Server Driver in the Data Source Administrator)Hi Brett,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to connect to your SQL
Server with FQDN name via VPN. If I have misunderstood your concern, please
feel free to point it out.
Based on my knowledge, if you connect via VPN, your client will be on the
intranet and please make sure your intranet DNS server could resolve that
FQDN. What do your intranet clients used to connect the SQL Server? server
name, ip address or FQDN name?
Please connect via VPN and perform the test below
1. PING with FQDN name and IP address (make sure you are using it's
intranet IP address)
2. telnet <FQDN name or IP address> <Port number, usually 1433>
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||I'd like my intranet clients (and extranet clients) to be able to connect
using the FQDN -- but you're correct they cannot resolve that name right
now on the intranet (or if behind the VPN). Why would that be? They can
ping the www.mydomain.com, but if I use www.mydomain.com as the server name,
it isn't resolved. How can I get it to resolve the FQDN for a SQL Server
ODBC connection?
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:7H$Voa50FHA.3328@.TK2MSFTNGXA01.phx.gbl...
> Hi Brett,
> Welcome to use MSDN Managed Newsgroup!
> From your descriptions, I understood you would like to connect to your SQL
> Server with FQDN name via VPN. If I have misunderstood your concern,
> please
> feel free to point it out.
> Based on my knowledge, if you connect via VPN, your client will be on the
> intranet and please make sure your intranet DNS server could resolve that
> FQDN. What do your intranet clients used to connect the SQL Server? server
> name, ip address or FQDN name?
> Please connect via VPN and perform the test below
> 1. PING with FQDN name and IP address (make sure you are using it's
> intranet IP address)
> 2. telnet <FQDN name or IP address> <Port number, usually 1433>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Brett,
It seems none of your intranet clients (no matter it conncet directly in
the intranet or connnect via VPN) will be able to connet the SQL Server by
FQDN name. If I have misunderstood your concern, please feel free to point
it out.
Please perform the steps below and let me know the results
1. check whether there are any Firewall settings in your intranet to see
whether it blocks the connection.
2. perform the command below and let me know the result
TELNET <FQDN name or IP address> <Port number, usually 1433>
3. What's the IP address return back from ping www.mydomain.com. Does it
match the IP address of your SQL Server?
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.