Connectivity with a SQL instance.

Hi,
I installed on a failover cluster, one default virtual sql
server installation named SERVER1 and a other one
installation of virtual server with instance name called
server2\ufa.
When I try to connect true a firewall on SERVER1 with an
UDL file the connection is OK, but When I'm trying the
same test on SERVER2\ufa I've got this error :
[DBNETLIB][ConnectionOpen (Invalid Instance()).]
When I pinging SERVER1 it's OK, When I trying a telnet
command on IP adresse 1433 it's also OK
But on virtual SERVER2 the port 1433 is on the name
SERVER2 so when I try a command telnet SERVER2 1433 it's
OK but SERVER2\UFA is not recognize.
On the firewall the port 1433 is open for Both IP adresses
and we openned also the ports 139 and 445 for the Netbios.
( Name Pipes )
Someone can help me ?You probably need to open up port 1434 or better yet, open the Server
Network Utility on SERVER2 and set the port explicity to something other
than 1433 or 1434, then connect using the port number rather than the
instance name. This is well documented in Books on Line under firewalls.
"Pierre" <anonymous@.discussions.microsoft.com> wrote in message
news:bfde01c4086c$a9472a00$a501280a@.phx.gbl...
> Hi,
> I installed on a failover cluster, one default virtual sql
> server installation named SERVER1 and a other one
> installation of virtual server with instance name called
> server2\ufa.
> When I try to connect true a firewall on SERVER1 with an
> UDL file the connection is OK, but When I'm trying the
> same test on SERVER2\ufa I've got this error :
> [DBNETLIB][ConnectionOpen (Invalid Instance()).]
> When I pinging SERVER1 it's OK, When I trying a telnet
> command on IP adresse 1433 it's also OK
> But on virtual SERVER2 the port 1433 is on the name
> SERVER2 so when I try a command telnet SERVER2 1433 it's
> OK but SERVER2\UFA is not recognize.
> On the firewall the port 1433 is open for Both IP adresses
> and we openned also the ports 139 and 445 for the Netbios.
> ( Name Pipes )
> Someone can help me ?
>|||To be able to connect to a named instance, (both clustered and stand alone)
1) If the client has MDAC 2.5 or lower, then you have to specify the port
number when connecting and make sure this port is open at the firewall.
2)If the client has MDAC 2.6 or higher, then specify the port number when
connecting or make sure UDP port 1434 is open at the firewall (This is true
even if the named instance is listening on port 1433)
The following articles maybe helpful.
286303 INF: Behavior of SQL Server 2000 Network Library During Dynamic Port
http://support.microsoft.com/?id=286303
287932 INF: TCP Ports Needed for Communication to SQL Server Through a
Firewall
http://support.microsoft.com/?id=287932
286303 INF: Behavior of SQL Server 2000 Network Library During Dynamic Port
http://support.microsoft.com/?id=286303
265808 INF: How to Connect to an SQL Server 2000 Named Instance with the
http://support.microsoft.com/?id=265808
Thanks
Deepali
This posting is provided "AS IS" with no warranties, and confers no rights

Connectivity Issues - Bizzare

I am trying to connect to a default instance of SQL Server 2000 Enterprise on
a remote network server using ADO.NET objects through ASP.NET Web Application.
The SQL SERVER is on a Windows XP machine (no firewall enabled) with TCP/IP
and Named Pipes enabled
For the test purposes, I created a new Web Form, created a new SqlConnection
in the Server Explorer to the remote database. I test the connection is VS
IDE and everything is fine. I create a SqlDataAdapter on the web form,
generate a dataset, test the dataset and the results are valid. I bind the
SqlDataAdapter to a datagrid and run the webform. I now get the following
error.
System.Data.SqlClient.SqlException: SQL Server does not exist or access
denied.
at System.Data.SqlClient.ConnectionPool.GetConnection (Boolean&
isInTransaction)
at
System.Data.SqlClient.SqlConnectionPoolManager.Get PooledConnection(SqlConnectionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at DBConnTest.WebForm1.Page_Load(Object sender, EventArgs e) in
c:\inetpub\wwwroot\dbconntest\webform1.aspx.cs:lin e 39
Hmph! I take the same connection string from the web app, create a win app.
Place a datagrid on the form, do the binding and load the app. Hey Presto! it
works.
I used TCPView (systeminternals.com) and I can see aspnet_wp trying to
create a connection, but its at though SQL Server is rejecting the
connection, although I see no failures in SQL Server.
Any thoughts would be much appreciated.
Andy
Sounds like firewall to me.
As a quick test to see if firewall is blocking port, try this from command
prompt on client machine ->
telnet mysqlmachine 1433
If you can't telnet to 1433, then you have firewall issue.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"Andy Furnival" <AndyFurnival@.discussions.microsoft.com> wrote in message
news:40AC00C0-0402-452B-AAC8-F25D97E94ACD@.microsoft.com...
>I am trying to connect to a default instance of SQL Server 2000 Enterprise
>on
> a remote network server using ADO.NET objects through ASP.NET Web
> Application.
> The SQL SERVER is on a Windows XP machine (no firewall enabled) with
> TCP/IP
> and Named Pipes enabled
> For the test purposes, I created a new Web Form, created a new
> SqlConnection
> in the Server Explorer to the remote database. I test the connection is VS
> IDE and everything is fine. I create a SqlDataAdapter on the web form,
> generate a dataset, test the dataset and the results are valid. I bind the
> SqlDataAdapter to a datagrid and run the webform. I now get the following
> error.
> System.Data.SqlClient.SqlException: SQL Server does not exist or access
> denied.
> at System.Data.SqlClient.ConnectionPool.GetConnection (Boolean&
> isInTransaction)
> at
> System.Data.SqlClient.SqlConnectionPoolManager.Get PooledConnection(SqlConnectionString
> options, Boolean& isInTransaction)
> at System.Data.SqlClient.SqlConnection.Open()
> at DBConnTest.WebForm1.Page_Load(Object sender, EventArgs e) in
> c:\inetpub\wwwroot\dbconntest\webform1.aspx.cs:lin e 39
> Hmph! I take the same connection string from the web app, create a win
> app.
> Place a datagrid on the form, do the binding and load the app. Hey Presto!
> it
> works.
> I used TCPView (systeminternals.com) and I can see aspnet_wp trying to
> create a connection, but its at though SQL Server is rejecting the
> connection, although I see no failures in SQL Server.
> Any thoughts would be much appreciated.
> Andy

Connectivity Issues - Bizzare

I am trying to connect to a default instance of SQL Server 2000 Enterprise o
n
a remote network server using ADO.NET objects through ASP.NET Web Applicatio
n.
The SQL SERVER is on a Windows XP machine (no firewall enabled) with TCP/IP
and Named Pipes enabled
For the test purposes, I created a new Web Form, created a new SqlConnection
in the Server Explorer to the remote database. I test the connection is VS
IDE and everything is fine. I create a SqlDataAdapter on the web form,
generate a dataset, test the dataset and the results are valid. I bind the
SqlDataAdapter to a datagrid and run the webform. I now get the following
error.
System.Data.SqlClient.SqlException: SQL Server does not exist or access
denied.
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
isInTransaction)
at
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
tionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at DBConnTest.WebForm1.Page_Load(Object sender, EventArgs e) in
c:\inetpub\wwwroot\dbconntest\webform1.aspx.cs:line 39
Hmph! I take the same connection string from the web app, create a win app.
Place a datagrid on the form, do the binding and load the app. Hey Presto! i
t
works.
I used TCPView (systeminternals.com) and I can see aspnet_wp trying to
create a connection, but its at though SQL Server is rejecting the
connection, although I see no failures in SQL Server.
Any thoughts would be much appreciated.
AndySounds like firewall to me.
As a quick test to see if firewall is blocking port, try this from command
prompt on client machine ->
telnet mysqlmachine 1433
If you can't telnet to 1433, then you have firewall issue.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"Andy Furnival" <AndyFurnival@.discussions.microsoft.com> wrote in message
news:40AC00C0-0402-452B-AAC8-F25D97E94ACD@.microsoft.com...
>I am trying to connect to a default instance of SQL Server 2000 Enterprise
>on
> a remote network server using ADO.NET objects through ASP.NET Web
> Application.
> The SQL SERVER is on a Windows XP machine (no firewall enabled) with
> TCP/IP
> and Named Pipes enabled
> For the test purposes, I created a new Web Form, created a new
> SqlConnection
> in the Server Explorer to the remote database. I test the connection is VS
> IDE and everything is fine. I create a SqlDataAdapter on the web form,
> generate a dataset, test the dataset and the results are valid. I bind the
> SqlDataAdapter to a datagrid and run the webform. I now get the following
> error.
> System.Data.SqlClient.SqlException: SQL Server does not exist or access
> denied.
> at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
> isInTransaction)
> at
> System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConn
ectionString
> options, Boolean& isInTransaction)
> at System.Data.SqlClient.SqlConnection.Open()
> at DBConnTest.WebForm1.Page_Load(Object sender, EventArgs e) in
> c:\inetpub\wwwroot\dbconntest\webform1.aspx.cs:line 39
> Hmph! I take the same connection string from the web app, create a win
> app.
> Place a datagrid on the form, do the binding and load the app. Hey Presto!
> it
> works.
> I used TCPView (systeminternals.com) and I can see aspnet_wp trying to
> create a connection, but its at though SQL Server is rejecting the
> connection, although I see no failures in SQL Server.
> Any thoughts would be much appreciated.
> Andy

Connectivity issue with SQL 2005 Developer Edition Named Instance

Hi,

I have installed one default and one named instance of SQL Server 2005 Dev. Editition SP2 on my server.

The Named service is configured to use Shared, NamedPipe and TCP (Port 4333).

There is no firewall on the machine. But still i am not able to connect to it form remote computer.

can any one help me with these. this is urgent.

Thanks.

KV

I found it. My SQL Browser service was running under domain account which was not working, i have configured it to run under NetworkService and it has started. sqlsql

Connectivity Issue

Hi Folks,

Im a bit stuck with this one.

Have a SQL 2000 Instance that we require a load of clients to connect to.

It seems that as soon as we get the MSSQL$Service to run under a domain account we can only authenticate to SQL Server if we set up a client alias.

Under a local system account it seems to authenticate without any issues.

This is a real pain as I don't want to have to set client ailas up on every one of my clients.

Anybody seen this sort of issue before.

Thanks in advanceAddtional Info :

The domain account that is running the mssql$service is in a differnet domain from the users that are connecting to SQL Server. There is a trust in place between the two domains.

The connection error is your usual null user not associated with a trusted connection.

The error log says SQL is listening on TCP, Shared Mem and Named Pipes|||Sorted

Bloody Group Policy on the service account.

Service Account had to get additional permissions.

Thanks|||This sounds cool .. any idea what permissions were required for the service accounts ?

Thanks,
-Ranjit

----------------------
Its OK to be a fool for 5 minutes than for the rest of your life ( Old Japanese Proverb )|||To summarise the problem for future reference:

Situation
Windows Server 2003 in RBSRES01.Net domain
SQL Server 2000 + SP4 (and any other appropriate patches)
SQL Service running under domain account (either EUROPA or RBSRES01)

Connection From
Enterprise Manager or ODBC (DSN or Ms Access)
Any client machine (workstation or server) without a named pipe client alias setup

Error
Server: Msg 18452, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Root Cause
As per the below Microsoft KB article the account running SQL server must have certain windows rights. To grant these rights under the RBSRES01 group policy setup, the account needs to be a member of the following domain local security groups:
sSRV-AssignPrimaryToken-Privilege
sSRV-BatchLogon-Right
sSRV-ChangeNotify-Privilege
sSRV-EnableDelegation-Privilege
sSRV-Impersonate-Privilege
sSRV-LockMemory-Privilege
sSRV-ServiceLogon-Right

http://support.microsoft.com/kb/840219

Simply adding the account to the local Admins group will not suffice!

connectivity

dears,
I've installed one sql server instance .
It is connecting through Named pipes only
Through TCP/IP its not connecting.
Only the default instance in the machine is connecting through all
protocols
Why is it like that ?
Please help me...chaluvadi specialists (sanujss@.gmail.com) writes:
> dears,
> I've installed one sql server instance .
> It is connecting through Named pipes only
> Through TCP/IP its not connecting.
> Only the default instance in the machine is connecting through all
> protocols
> Why is it like that ?
> Please help me...
Which versions of SQL Server are the two instances running?
How do you conclude that you cannot connect to the named instance
through TCP/IP?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> I've installed one sql server instance .
> It is connecting through Named pipes only
> Through TCP/IP its not connecting.
> Only the default instance in the machine is connecting through all
> protocols
Let's get the obvious out of the way:
What is the possibility that there is a firewall between your client and
your server that is blocking TCP:1433?
--
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei

Connection: "(local)" vs "ComputerName"

Hi,
I was reading in a book about SQL 2005 about how connections are made to SQL
2005. Assume I have a SQL Server instance named SQL1 and I have a client app
installed on SQL1. The book claimed that if the app's connection string
sets the datasource to "(local)" then the connection uses Shared Memory, and
if it set the datasource to the computer's name, "SQL1", that it uses the
TCP/IP stack which is slower than Shared Memory.
Reading BOL for SQL 2000, it seems to indicate that a local client will
always use Shared Memory, regardless of how the datasource is specified.
Can someone provide a definitive clarification and indicate whether it is
different for SQL 2000 and SQL 2005?
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
Hi Daniel,
I am afraid that the book description is not correct. By default any
connection with the following names from local computer will use Shared
Memory protocol:
1. "<computer_name>"
2. "<computer_name>\<instance_name>" for a named instance
3. "(local)"
4. "(local)\<instance_name>" for a named instance
5. "Localhost"
6. "localhost\<instance_name>" for a named instance
7. A single period "."
8. ".\<instance_name>" for a named instance
A local connection may use another protocol under the following
circumstances:
1. Connect to a client alias that specifies a protocol.
2. Prefix the computer name with the protocol (for example,
"np:<computer_name>" or "tcp:<computer_name>").
3. Connect to the IP address which results in a TCP/IP connection.
4. Connect to the fully qualified domain name (FQDN) which results in a
TCP/IP connection (for example, "<computer_name>.<domain_name>.com"
You can run the following statement to check the current protocol for the
local connections:
SELECT net_transport
FROM sys.dm_exec_connections
WHERE session_id = @.@.SPID;
For more information, you may refer to:
Default Client Connection Behavior
http://msdn2.microsoft.com/en-us/library/ms190204.aspx
One interesting thing is that for the forms of <computer_name> and
<computer_name>\<instance_name>, they do not always use Shared Memory
protocol. They just by default use Shared Memory protocol.
Actually in registry there is a value that records which protocol is used
in the latest connection. For the next time local connection, it will use
the latest protocol.
For example:
--This results in Shared Memory connection
sqlcmd /SCharlesXP /E
--This results in Named Pipes connection
sqlcmd /Snp:CharlesXP /E
--This keeps using Named Pipes connection
sqlcmd /SCharlesXP /E
--This results in Shared Memory connection
sqlcmd /Slpc:CharlesXP /E
--This keeps using Shared Memory connection
sqlcmd /SCharlesXP /E
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
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.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Charles;
Have you tried it on SQL2005 SP2 running on XP? I tried on two such
instances. Even with Named Pipes disabled, the following query:
SELECT net_transport
FROM sys.dm_exec_connections
WHERE session_id = @.@.SPID;
still returns Named Pipes for many of the server name settings in your first
group, which is supposed to use Shared Memory.
On W2K3 servers, I didn't see this behavior.
Linchi
"Charles Wang[MSFT]" wrote:

> Hi Daniel,
> I am afraid that the book description is not correct. By default any
> connection with the following names from local computer will use Shared
> Memory protocol:
> 1. "<computer_name>"
> 2. "<computer_name>\<instance_name>" for a named instance
> 3. "(local)"
> 4. "(local)\<instance_name>" for a named instance
> 5. "Localhost"
> 6. "localhost\<instance_name>" for a named instance
> 7. A single period "."
> 8. ".\<instance_name>" for a named instance
> A local connection may use another protocol under the following
> circumstances:
> 1. Connect to a client alias that specifies a protocol.
> 2. Prefix the computer name with the protocol (for example,
> "np:<computer_name>" or "tcp:<computer_name>").
> 3. Connect to the IP address which results in a TCP/IP connection.
> 4. Connect to the fully qualified domain name (FQDN) which results in a
> TCP/IP connection (for example, "<computer_name>.<domain_name>.com"
> You can run the following statement to check the current protocol for the
> local connections:
> SELECT net_transport
> FROM sys.dm_exec_connections
> WHERE session_id = @.@.SPID;
> For more information, you may refer to:
> Default Client Connection Behavior
> http://msdn2.microsoft.com/en-us/library/ms190204.aspx
> One interesting thing is that for the forms of <computer_name> and
> <computer_name>\<instance_name>, they do not always use Shared Memory
> protocol. They just by default use Shared Memory protocol.
> Actually in registry there is a value that records which protocol is used
> in the latest connection. For the next time local connection, it will use
> the latest protocol.
> For example:
> --This results in Shared Memory connection
> sqlcmd /SCharlesXP /E
> --This results in Named Pipes connection
> sqlcmd /Snp:CharlesXP /E
> --This keeps using Named Pipes connection
> sqlcmd /SCharlesXP /E
> --This results in Shared Memory connection
> sqlcmd /Slpc:CharlesXP /E
> --This keeps using Shared Memory connection
> sqlcmd /SCharlesXP /E
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
>
> 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.
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
|||Hi Linchi,
Did you restart your SQL Server service after you disable Named Pipes
protocol on your server by using SQL Server Configuration Manager? or did
you just disable the Named Pipes in SQL Native Client Configuration on your
computer? Per my test, it is no use of disabling Named Pipes on client;
but Shared Memory will be used if you disable the Named Pipes protocol on
server.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
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.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Charles,
Thank you very much for the detailed response.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:kdagNzOnHHA.5168@.TK2MSFTNGHUB02.phx.gbl...
> Hi Daniel,
> I am afraid that the book description is not correct. By default any
> connection with the following names from local computer will use Shared
> Memory protocol:
> 1. "<computer_name>"
> 2. "<computer_name>\<instance_name>" for a named instance
> 3. "(local)"
> 4. "(local)\<instance_name>" for a named instance
> 5. "Localhost"
> 6. "localhost\<instance_name>" for a named instance
> 7. A single period "."
> 8. ".\<instance_name>" for a named instance
> A local connection may use another protocol under the following
> circumstances:
> 1. Connect to a client alias that specifies a protocol.
> 2. Prefix the computer name with the protocol (for example,
> "np:<computer_name>" or "tcp:<computer_name>").
> 3. Connect to the IP address which results in a TCP/IP connection.
> 4. Connect to the fully qualified domain name (FQDN) which results in a
> TCP/IP connection (for example, "<computer_name>.<domain_name>.com"
> You can run the following statement to check the current protocol for the
> local connections:
> SELECT net_transport
> FROM sys.dm_exec_connections
> WHERE session_id = @.@.SPID;
> For more information, you may refer to:
> Default Client Connection Behavior
> http://msdn2.microsoft.com/en-us/library/ms190204.aspx
> One interesting thing is that for the forms of <computer_name> and
> <computer_name>\<instance_name>, they do not always use Shared Memory
> protocol. They just by default use Shared Memory protocol.
> Actually in registry there is a value that records which protocol is used
> in the latest connection. For the next time local connection, it will use
> the latest protocol.
> For example:
> --This results in Shared Memory connection
> sqlcmd /SCharlesXP /E
> --This results in Named Pipes connection
> sqlcmd /Snp:CharlesXP /E
> --This keeps using Named Pipes connection
> sqlcmd /SCharlesXP /E
> --This results in Shared Memory connection
> sqlcmd /Slpc:CharlesXP /E
> --This keeps using Shared Memory connection
> sqlcmd /SCharlesXP /E
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> 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.
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ================================================== ====
>
>
|||Positive, I did restart the SQL Server service. In fact, I just tried a few
more times to make sure I wasn't hallucinating.
Linchi
"Charles Wang[MSFT]" wrote:

> Hi Linchi,
> Did you restart your SQL Server service after you disable Named Pipes
> protocol on your server by using SQL Server Configuration Manager? or did
> you just disable the Named Pipes in SQL Native Client Configuration on your
> computer? Per my test, it is no use of disabling Named Pipes on client;
> but Shared Memory will be used if you disable the Named Pipes protocol on
> server.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
>
> 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.
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
>
>
>
|||Hi Linchi,
It is strange. I have checked that on my computer and I could not reproduce
your issue. My OS is also Windows XP. I also checked that on a Windows 2K
x64 computer. It seemed that the Named Pipes was not disabled on your SQL
Server. Could you please check your SQL Server error logs to see if the
Named Pipes protocol was not disabled during the service startup? or you
may check if you can reproduce your issue on another computer.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Connection: "(local)" vs "ComputerName"

Hi,
I was reading in a book about SQL 2005 about how connections are made to SQL
2005. Assume I have a SQL Server instance named SQL1 and I have a client app
installed on SQL1. The book claimed that if the app's connection string
sets the datasource to "(local)" then the connection uses Shared Memory, and
if it set the datasource to the computer's name, "SQL1", that it uses the
TCP/IP stack which is slower than Shared Memory.
Reading BOL for SQL 2000, it seems to indicate that a local client will
always use Shared Memory, regardless of how the datasource is specified.
Can someone provide a definitive clarification and indicate whether it is
different for SQL 2000 and SQL 2005?
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.orgHi Daniel,
I am afraid that the book description is not correct. By default any
connection with the following names from local computer will use Shared
Memory protocol:
1. "<computer_name>"
2. "<computer_name>\<instance_name>" for a named instance
3. "(local)"
4. "(local)\<instance_name>" for a named instance
5. "Localhost"
6. "localhost\<instance_name>" for a named instance
7. A single period "."
8. ".\<instance_name>" for a named instance
A local connection may use another protocol under the following
circumstances:
1. Connect to a client alias that specifies a protocol.
2. Prefix the computer name with the protocol (for example,
"np:<computer_name>" or "tcp:<computer_name>").
3. Connect to the IP address which results in a TCP/IP connection.
4. Connect to the fully qualified domain name (FQDN) which results in a
TCP/IP connection (for example, "<computer_name>.<domain_name>.com"
You can run the following statement to check the current protocol for the
local connections:
SELECT net_transport
FROM sys.dm_exec_connections
WHERE session_id = @.@.SPID;
For more information, you may refer to:
Default Client Connection Behavior
http://msdn2.microsoft.com/en-us/library/ms190204.aspx
One interesting thing is that for the forms of <computer_name> and
<computer_name>\<instance_name>, they do not always use Shared Memory
protocol. They just by default use Shared Memory protocol.
Actually in registry there is a value that records which protocol is used
in the latest connection. For the next time local connection, it will use
the latest protocol.
For example:
--This results in Shared Memory connection
sqlcmd /SCharlesXP /E
--This results in Named Pipes connection
sqlcmd /Snp:CharlesXP /E
--This keeps using Named Pipes connection
sqlcmd /SCharlesXP /E
--This results in Shared Memory connection
sqlcmd /Slpc:CharlesXP /E
--This keeps using Shared Memory connection
sqlcmd /SCharlesXP /E
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
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/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Charles;
Have you tried it on SQL2005 SP2 running on XP? I tried on two such
instances. Even with Named Pipes disabled, the following query:
SELECT net_transport
FROM sys.dm_exec_connections
WHERE session_id = @.@.SPID;
still returns Named Pipes for many of the server name settings in your first
group, which is supposed to use Shared Memory.
On W2K3 servers, I didn't see this behavior.
Linchi
"Charles Wang[MSFT]" wrote:

> Hi Daniel,
> I am afraid that the book description is not correct. By default any
> connection with the following names from local computer will use Shared
> Memory protocol:
> 1. "<computer_name>"
> 2. "<computer_name>\<instance_name>" for a named instance
> 3. "(local)"
> 4. "(local)\<instance_name>" for a named instance
> 5. "Localhost"
> 6. "localhost\<instance_name>" for a named instance
> 7. A single period "."
> 8. ".\<instance_name>" for a named instance
> A local connection may use another protocol under the following
> circumstances:
> 1. Connect to a client alias that specifies a protocol.
> 2. Prefix the computer name with the protocol (for example,
> "np:<computer_name>" or "tcp:<computer_name>").
> 3. Connect to the IP address which results in a TCP/IP connection.
> 4. Connect to the fully qualified domain name (FQDN) which results in a
> TCP/IP connection (for example, "<computer_name>.<domain_name>.com"
> You can run the following statement to check the current protocol for the
> local connections:
> SELECT net_transport
> FROM sys.dm_exec_connections
> WHERE session_id = @.@.SPID;
> For more information, you may refer to:
> Default Client Connection Behavior
> http://msdn2.microsoft.com/en-us/library/ms190204.aspx
> One interesting thing is that for the forms of <computer_name> and
> <computer_name>\<instance_name>, they do not always use Shared Memory
> protocol. They just by default use Shared Memory protocol.
> Actually in registry there is a value that records which protocol is used
> in the latest connection. For the next time local connection, it will use
> the latest protocol.
> For example:
> --This results in Shared Memory connection
> sqlcmd /SCharlesXP /E
> --This results in Named Pipes connection
> sqlcmd /Snp:CharlesXP /E
> --This keeps using Named Pipes connection
> sqlcmd /SCharlesXP /E
> --This results in Shared Memory connection
> sqlcmd /Slpc:CharlesXP /E
> --This keeps using Shared Memory connection
> sqlcmd /SCharlesXP /E
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> Get notification to my posts through email? Please refer to:
> [url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif[/ur
l]
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly
.
>
> 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/subscript...t/default.aspx.
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>
>|||Hi Linchi,
Did you restart your SQL Server service after you disable Named Pipes
protocol on your server by using SQL Server Configuration Manager? or did
you just disable the Named Pipes in SQL Native Client Configuration on your
computer? Per my test, it is no use of disabling Named Pipes on client;
but Shared Memory will be used if you disable the Named Pipes protocol on
server.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
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/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Charles,
Thank you very much for the detailed response.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:kdagNzOnHHA.5168@.TK2MSFTNGHUB02.phx.gbl...
> Hi Daniel,
> I am afraid that the book description is not correct. By default any
> connection with the following names from local computer will use Shared
> Memory protocol:
> 1. "<computer_name>"
> 2. "<computer_name>\<instance_name>" for a named instance
> 3. "(local)"
> 4. "(local)\<instance_name>" for a named instance
> 5. "Localhost"
> 6. "localhost\<instance_name>" for a named instance
> 7. A single period "."
> 8. ".\<instance_name>" for a named instance
> A local connection may use another protocol under the following
> circumstances:
> 1. Connect to a client alias that specifies a protocol.
> 2. Prefix the computer name with the protocol (for example,
> "np:<computer_name>" or "tcp:<computer_name>").
> 3. Connect to the IP address which results in a TCP/IP connection.
> 4. Connect to the fully qualified domain name (FQDN) which results in a
> TCP/IP connection (for example, "<computer_name>.<domain_name>.com"
> You can run the following statement to check the current protocol for the
> local connections:
> SELECT net_transport
> FROM sys.dm_exec_connections
> WHERE session_id = @.@.SPID;
> For more information, you may refer to:
> Default Client Connection Behavior
> http://msdn2.microsoft.com/en-us/library/ms190204.aspx
> One interesting thing is that for the forms of <computer_name> and
> <computer_name>\<instance_name>, they do not always use Shared Memory
> protocol. They just by default use Shared Memory protocol.
> Actually in registry there is a value that records which protocol is used
> in the latest connection. For the next time local connection, it will use
> the latest protocol.
> For example:
> --This results in Shared Memory connection
> sqlcmd /SCharlesXP /E
> --This results in Named Pipes connection
> sqlcmd /Snp:CharlesXP /E
> --This keeps using Named Pipes connection
> sqlcmd /SCharlesXP /E
> --This results in Shared Memory connection
> sqlcmd /Slpc:CharlesXP /E
> --This keeps using Shared Memory connection
> sqlcmd /SCharlesXP /E
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> Get notification to my posts through email? Please refer to:
> [url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif[/ur
l]
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> 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/subscript...t/default.aspx.
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>
>|||Positive, I did restart the SQL Server service. In fact, I just tried a few
more times to make sure I wasn't hallucinating.
Linchi
"Charles Wang[MSFT]" wrote:

> Hi Linchi,
> Did you restart your SQL Server service after you disable Named Pipes
> protocol on your server by using SQL Server Configuration Manager? or did
> you just disable the Named Pipes in SQL Native Client Configuration on you
r
> computer? Per my test, it is no use of disabling Named Pipes on client;
> but Shared Memory will be used if you disable the Named Pipes protocol on
> server.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> Get notification to my posts through email? Please refer to:
> [url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif[/ur
l]
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly
.
>
> 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/subscript...t/default.aspx.
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>
>
>
>
>|||Hi Linchi,
It is strange. I have checked that on my computer and I could not reproduce
your issue. My OS is also Windows XP. I also checked that on a Windows 2K
x64 computer. It seemed that the Named Pipes was not disabled on your SQL
Server. Could you please check your SQL Server error logs to see if the
Named Pipes protocol was not disabled during the service startup? or you
may check if you can reproduce your issue on another computer.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

Connection: "(local)" vs "ComputerName"

Hi,
I was reading in a book about SQL 2005 about how connections are made to SQL
2005. Assume I have a SQL Server instance named SQL1 and I have a client app
installed on SQL1. The book claimed that if the app's connection string
sets the datasource to "(local)" then the connection uses Shared Memory, and
if it set the datasource to the computer's name, "SQL1", that it uses the
TCP/IP stack which is slower than Shared Memory.
Reading BOL for SQL 2000, it seems to indicate that a local client will
always use Shared Memory, regardless of how the datasource is specified.
Can someone provide a definitive clarification and indicate whether it is
different for SQL 2000 and SQL 2005?
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.orgHi Daniel,
I am afraid that the book description is not correct. By default any
connection with the following names from local computer will use Shared
Memory protocol:
1. "<computer_name>"
2. "<computer_name>\<instance_name>" for a named instance
3. "(local)"
4. "(local)\<instance_name>" for a named instance
5. "Localhost"
6. "localhost\<instance_name>" for a named instance
7. A single period "."
8. ".\<instance_name>" for a named instance
A local connection may use another protocol under the following
circumstances:
1. Connect to a client alias that specifies a protocol.
2. Prefix the computer name with the protocol (for example,
"np:<computer_name>" or "tcp:<computer_name>").
3. Connect to the IP address which results in a TCP/IP connection.
4. Connect to the fully qualified domain name (FQDN) which results in a
TCP/IP connection (for example, "<computer_name>.<domain_name>.com"
You can run the following statement to check the current protocol for the
local connections:
SELECT net_transport
FROM sys.dm_exec_connections
WHERE session_id = @.@.SPID;
For more information, you may refer to:
Default Client Connection Behavior
http://msdn2.microsoft.com/en-us/library/ms190204.aspx
One interesting thing is that for the forms of <computer_name> and
<computer_name>\<instance_name>, they do not always use Shared Memory
protocol. They just by default use Shared Memory protocol.
Actually in registry there is a value that records which protocol is used
in the latest connection. For the next time local connection, it will use
the latest protocol.
For example:
--This results in Shared Memory connection
sqlcmd /SCharlesXP /E
--This results in Named Pipes connection
sqlcmd /Snp:CharlesXP /E
--This keeps using Named Pipes connection
sqlcmd /SCharlesXP /E
--This results in Shared Memory connection
sqlcmd /Slpc:CharlesXP /E
--This keeps using Shared Memory connection
sqlcmd /SCharlesXP /E
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
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.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Charles;
Have you tried it on SQL2005 SP2 running on XP? I tried on two such
instances. Even with Named Pipes disabled, the following query:
SELECT net_transport
FROM sys.dm_exec_connections
WHERE session_id = @.@.SPID;
still returns Named Pipes for many of the server name settings in your first
group, which is supposed to use Shared Memory.
On W2K3 servers, I didn't see this behavior.
Linchi
"Charles Wang[MSFT]" wrote:
> Hi Daniel,
> I am afraid that the book description is not correct. By default any
> connection with the following names from local computer will use Shared
> Memory protocol:
> 1. "<computer_name>"
> 2. "<computer_name>\<instance_name>" for a named instance
> 3. "(local)"
> 4. "(local)\<instance_name>" for a named instance
> 5. "Localhost"
> 6. "localhost\<instance_name>" for a named instance
> 7. A single period "."
> 8. ".\<instance_name>" for a named instance
> A local connection may use another protocol under the following
> circumstances:
> 1. Connect to a client alias that specifies a protocol.
> 2. Prefix the computer name with the protocol (for example,
> "np:<computer_name>" or "tcp:<computer_name>").
> 3. Connect to the IP address which results in a TCP/IP connection.
> 4. Connect to the fully qualified domain name (FQDN) which results in a
> TCP/IP connection (for example, "<computer_name>.<domain_name>.com"
> You can run the following statement to check the current protocol for the
> local connections:
> SELECT net_transport
> FROM sys.dm_exec_connections
> WHERE session_id = @.@.SPID;
> For more information, you may refer to:
> Default Client Connection Behavior
> http://msdn2.microsoft.com/en-us/library/ms190204.aspx
> One interesting thing is that for the forms of <computer_name> and
> <computer_name>\<instance_name>, they do not always use Shared Memory
> protocol. They just by default use Shared Memory protocol.
> Actually in registry there is a value that records which protocol is used
> in the latest connection. For the next time local connection, it will use
> the latest protocol.
> For example:
> --This results in Shared Memory connection
> sqlcmd /SCharlesXP /E
> --This results in Named Pipes connection
> sqlcmd /Snp:CharlesXP /E
> --This keeps using Named Pipes connection
> sqlcmd /SCharlesXP /E
> --This results in Shared Memory connection
> sqlcmd /Slpc:CharlesXP /E
> --This keeps using Shared Memory connection
> sqlcmd /SCharlesXP /E
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
>
> 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.
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>
>|||Hi Linchi,
Did you restart your SQL Server service after you disable Named Pipes
protocol on your server by using SQL Server Configuration Manager? or did
you just disable the Named Pipes in SQL Native Client Configuration on your
computer? Per my test, it is no use of disabling Named Pipes on client;
but Shared Memory will be used if you disable the Named Pipes protocol on
server.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
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.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Charles,
Thank you very much for the detailed response.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:kdagNzOnHHA.5168@.TK2MSFTNGHUB02.phx.gbl...
> Hi Daniel,
> I am afraid that the book description is not correct. By default any
> connection with the following names from local computer will use Shared
> Memory protocol:
> 1. "<computer_name>"
> 2. "<computer_name>\<instance_name>" for a named instance
> 3. "(local)"
> 4. "(local)\<instance_name>" for a named instance
> 5. "Localhost"
> 6. "localhost\<instance_name>" for a named instance
> 7. A single period "."
> 8. ".\<instance_name>" for a named instance
> A local connection may use another protocol under the following
> circumstances:
> 1. Connect to a client alias that specifies a protocol.
> 2. Prefix the computer name with the protocol (for example,
> "np:<computer_name>" or "tcp:<computer_name>").
> 3. Connect to the IP address which results in a TCP/IP connection.
> 4. Connect to the fully qualified domain name (FQDN) which results in a
> TCP/IP connection (for example, "<computer_name>.<domain_name>.com"
> You can run the following statement to check the current protocol for the
> local connections:
> SELECT net_transport
> FROM sys.dm_exec_connections
> WHERE session_id = @.@.SPID;
> For more information, you may refer to:
> Default Client Connection Behavior
> http://msdn2.microsoft.com/en-us/library/ms190204.aspx
> One interesting thing is that for the forms of <computer_name> and
> <computer_name>\<instance_name>, they do not always use Shared Memory
> protocol. They just by default use Shared Memory protocol.
> Actually in registry there is a value that records which protocol is used
> in the latest connection. For the next time local connection, it will use
> the latest protocol.
> For example:
> --This results in Shared Memory connection
> sqlcmd /SCharlesXP /E
> --This results in Named Pipes connection
> sqlcmd /Snp:CharlesXP /E
> --This keeps using Named Pipes connection
> sqlcmd /SCharlesXP /E
> --This results in Shared Memory connection
> sqlcmd /Slpc:CharlesXP /E
> --This keeps using Shared Memory connection
> sqlcmd /SCharlesXP /E
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> 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.
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>
>|||Positive, I did restart the SQL Server service. In fact, I just tried a few
more times to make sure I wasn't hallucinating.
Linchi
"Charles Wang[MSFT]" wrote:
> Hi Linchi,
> Did you restart your SQL Server service after you disable Named Pipes
> protocol on your server by using SQL Server Configuration Manager? or did
> you just disable the Named Pipes in SQL Native Client Configuration on your
> computer? Per my test, it is no use of disabling Named Pipes on client;
> but Shared Memory will be used if you disable the Named Pipes protocol on
> server.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
>
> 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.
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>
>
>
>
>|||Hi Linchi,
It is strange. I have checked that on my computer and I could not reproduce
your issue. My OS is also Windows XP. I also checked that on a Windows 2K
x64 computer. It seemed that the Named Pipes was not disabled on your SQL
Server. Could you please check your SQL Server error logs to see if the
Named Pipes protocol was not disabled during the service startup? or you
may check if you can reproduce your issue on another computer.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Connection to SQL Express ONLY with integrated security but WITHOUT domain

Does anybody know if it is possible to establish a connection to an sql express instance only with integrated security when this express instance is running on XP which is NOT part of a domain?

Thanks,

Klaus

If the SQL Express machine isn't connected to a domain then I don't believe Kerberos authentication will work but NTLM authentication should work. If the client application is running on the same machine as SQL Express then you should be able to straightforwardly give a user account local to the machine permission to connect to SQL Express (machine administrators will have it by default). If you're connecting from off-box then I think you'll need to setup the same account (same user name & password) on the remote machine.

Hope that helps.

Vaughn

Connection Timeout

Hi, I have a client application that submits heavy processing query to an instance of Sql Server 2005 (Enteriprise Edition). After a few seconds the following exception is raised: "The time available has expired before the completion of the operation or the server is not responding". If I submit to the same application light processing queries the exception is not raised.
The client application is written in VB.net and uses ADO.net. (SqlConnection, SqlCommand,.. classes). The "Timeout connection" property is set to 1000.
Any suggestion?
Thank you.

Hi Ivan,

The ConnectionTimeout property of SqlConnection defines the time boundaries to open a physical connection to the SQL Server. In your case, the physical connection seems to be established within a reasonable frame. However, after the connection is up, the client and the server begin exchanging security information as part of the login process. This is related to a different "login timeout", which unfortunately you don't seem to be able to control from SqlClient.

What is happening on the server during the login process is related to the SOS scheduler operations - I won't go into deep detail, but in a nutshell all the tasks (including processing of login information) are queued. If the queue length is very long, the login information processing may be delayed beyond the login timeout and you will experience your symptoms above. There could also be other factors - the login processing makes calls to the security subsystem, if the entire SQL machine is under heavy stress, those may take longer time, too. You should approach this as a perf problem and use appropriate tools like perfmon, the SOS DMVs, etc.

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Connection Timeout is the timeout for connecting to SQL Server. What you need is COMMAND timeout. Set COMMAND timeout=0 for indefinite period.

Command timeout has to be set on the command object - because it is related to a particular SQL processing.

Connection String with Named Instance and Port?

I am trying to create a SQL Server 2000 and 2005-friendly connection
string that will allow me to connect to a named instance on a
non-standard port. I can't get the syntax right, can anyone show an
example?
Thanks.
For data source, use YourServer\YourNamedInstance then a
comma followed by the port number - e.g.
Data Source=YourServer\NamedInstance,1433;
-Sue
On 23 Mar 2006 06:38:16 -0800, bradwiseathome@.hotmail.com
wrote:

>I am trying to create a SQL Server 2000 and 2005-friendly connection
>string that will allow me to connect to a named instance on a
>non-standard port. I can't get the syntax right, can anyone show an
>example?
>Thanks.

Connection String with Named Instance and Port?

I am trying to create a SQL Server 2000 and 2005-friendly connection
string that will allow me to connect to a named instance on a
non-standard port. I can't get the syntax right, can anyone show an
example?
Thanks.For data source, use YourServer\YourNamedInstance then a
comma followed by the port number - e.g.
Data Source=YourServer\NamedInstance,1433;
-Sue
On 23 Mar 2006 06:38:16 -0800, bradwiseathome@.hotmail.com
wrote:
>I am trying to create a SQL Server 2000 and 2005-friendly connection
>string that will allow me to connect to a named instance on a
>non-standard port. I can't get the syntax right, can anyone show an
>example?
>Thanks.

Connection String with Named Instance and Port?

I am trying to create a SQL Server 2000 and 2005-friendly connection
string that will allow me to connect to a named instance on a
non-standard port. I can't get the syntax right, can anyone show an
example?
Thanks.For data source, use YourServer\YourNamedInstance then a
comma followed by the port number - e.g.
Data Source=YourServer\NamedInstance,1433;
-Sue
On 23 Mar 2006 06:38:16 -0800, bradwiseathome@.hotmail.com
wrote:

>I am trying to create a SQL Server 2000 and 2005-friendly connection
>string that will allow me to connect to a named instance on a
>non-standard port. I can't get the syntax right, can anyone show an
>example?
>Thanks.

Connection String has Semicolon (;) - how on earth can I get this working?

Ok, here's my setup. I've got a named instance in a SQL 2000 cluster. I only have dbo rights on my database, because it is a shared infrastructure. Here's my current web.config connection string (the meat, anyway):

When I'm at the office, this is my connection string, pretty normal:

connectionString="Data Source=ServerName\InstanceName;Initial Catalog=blah..."

But, when I connect through the VPN, I can't just connect through the named instance - I have a specific port. This is where things get odd.

First, if I try to connect through SQL Server Management Studio (2005), i get nothing. If I try to connect using"ServerName\InstanceName, (comma) Port Number" it connects, but not to my instance. I get a seperate set of databases that I believe are in the default instance. So, I changed the comma to a semicolon (;) - and it still connected to the same thing - connected to the database, but to the wrong set of databases. So, on a whim, I tried plunking my string, which was now"ServerName\InstanceName;(semicolon) PortNumber" into the SQL 2000 Tools and it worked in both Query Analyzer and in Enterprise Manager. So, I thought, I'll just slam this into my connection string and all will be well. No. I can't use a semicolon in my connection string, and I can't find an escape character to use. Double semicolons don't work, a comma doesn't connect me properly, double colons don't work, the JDBC brackets don't work {} - so I'm at a loss. I'm out of ideas. I've set up aliases, and those don't work earlier.

I'm using ASP.net 2 with VB & C# and Visual Studio 2005 Professional. Thanks for any help anyone can give on this!

Have you tried with a Colon instead of a semicolon?

|||

I have - no dice. I've tried a colon after the servername but before the instance name (i.e.ServerName:Port\InstanceName) as well as at the end of the entire name (ServerName\InstanceName:Port).

|||

Hi jdandison,

The standard format to conifgure your port number in connection string is:string connectionString = "Server=Servername\InstanceName;port=your port number;"

So, try to change your connection string to :connectionString="Data Source=ServerName\InstanceName;port=NewPortNumber;Initial Catalog=blah..."and try again.

Tell us if you have any further questions. thanks

Connection string for default instance of SQL Server 2005, on a box also running a named i

The box I am trying to connect to is running two instances of SQL Server.

There is a SQL Server 2005 instance which is the default. There is a SQL Server 2000 instance which is named 'SQLSERVER'.

I can connect to the SQL Server 2000 instance no problem:

<addkey="ConnectionString"value="server=MYPC\SQLSERVER;database=mydatabase;user id=****;password=****"/>

However, I am having trouble connecting to the Default SQL Server 2005 instance. I have tried:

<addkey="ConnectionString"value="server=MYPC;database=mydatabase;user id=****;password=****"/>

but it doesn't work. I have tried explicitly setting SQL Server 2005 to use port 1434 (as SQL Server 2000 is running on port 1433), and then used:

<addkey="ConnectionString"value="server=MYPC,1434;database=mydatabase;user id=****;password=****"/>

but this doesn't work either.

Am I mssing something here? Any help much appreciated

Thanks...

Since both SQL Server 2000 and 2005 are running on the same machine, definitely one of the instance will be the named instance.

I guess SQL 2005 is a named instance. So the connection should be as follows:

server=MYPC\<Instance name>;database=mydatabase;user id=****;password=****

|||

Hi, thanks for the reply.

No, SQL Server 2000 was the named instance. SQL Server 2005 was the default. It was the default instance (SQL Server 2005) that I was having problems connecting to. Ineed, I was having trouble connecting to it with anything other than Enterprise Manager.

However, I have uninstalled / reinstalled SQL Server 2000 and SQL Server 2005, and made them both named instances. I can now connect to them both no problem.

I suspect that had I reinstalled with one or other of them being a default instance, I also would have had no problems. I had noticed a number of other strange behavioural issues in SQL Server 2005 and I suspect something had gone awry in general.

Connection Prodlem-with instance node

Hi
I have an active/passive sql server 2000 cluster. Most of the time I remote-login into the server and use the enterprise manager to work. But now when I click on either of the node I get the following error:
Encryption not supported on SQL Server ConnectionOpen (PreLoginHandshake())
But I'm able to connect to the server from my desktop.
Any help in this regard?
Thanks,
This means that the "Enable Protocol Encryption" option was set on the
machine you're using as a client.
Use the SQL Client Network Utility to uncheck this.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Hi Kevin
Thank you so much it worked.
Razi
"Kevin McDonnell [MSFT]" wrote:

> This means that the "Enable Protocol Encryption" option was set on the
> machine you're using as a client.
> Use the SQL Client Network Utility to uncheck this.
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
|||You're welcome!
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Connection Prodlem-with instance node

Hi
I have an active/passive sql server 2000 cluster. Most of the time I remote-
login into the server and use the enterprise manager to work. But now when I
click on either of the node I get the following error:
Encryption not supported on SQL Server ConnectionOpen (PreLoginHandshake())
But I'm able to connect to the server from my desktop.
Any help in this regard?
Thanks,This means that the "Enable Protocol Encryption" option was set on the
machine you're using as a client.
Use the SQL Client Network Utility to uncheck this.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi Kevin
Thank you so much it worked.
Razi
"Kevin McDonnell [MSFT]" wrote:

> This means that the "Enable Protocol Encryption" option was set on the
> machine you're using as a client.
> Use the SQL Client Network Utility to uncheck this.
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||You're welcome!
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Connection problems

I just installed 2005 and have an instance of Enterprise on my local machine. My SSAS service appears to be working properly in Services. However, whenever I try to deploy a cube (simple one from tutorial) or try to connect from Management Console, I get rejected. The error message is:

No connection could be made because the target machine actively refused it (System).

I've tried about everything I can think of and can't get past this. Any guidance would be greatly appreciated.

If you have a named instance installed (you can tell by looking at the service name "SQL Server Analysis Services (INSTANCE_NAME)"), then you need to set the Target Server project property (right click on the Analysis Services project in VS, use Properties, go to Deployment tab and set Server to "localhost\INSTANCE_NAME").

If you are disconnected from the network, try using 127.0.0.1 instead of machine name.

Adrian Dumitrascu

|||I'm dealing with the same problem. But as I can se I don't have an instance of Analysis Service running. I have just an instance of Database Engine (my_host_name\SQLExpress). How can I create an instance of Analysis Services? Please!!!|||

If you don't have the "SQL Server Analysis Services" service at all (default instance or named instance), you'll need to re-run Setup to install it.

Adrian Dumitrascu

Connection Problems

Hello,
I have a small workgroup of computers (4) all running windows xp sp2. One
of them is running msde2000. I can connect to a named instance of sql server
but can not connect to the default instance. I am using osql and using
trusted connection. I am not sure what other information is needed so
please do not hesitate to ask! I am really pulling my hair out on this one!
Any help would be greatly appreciated.
Hello,
Did you activated TCPIP network protocol for this default instance (verify
in the error.log for the instance)?
Hope this will help.
Philippe RUELLO
DBA MSSQL
"Michael O'S" <mroresolutions@.woh.rr.com> a crit dans le message de news:
NhMxd.3864$mA3.1811@.fe2.columbus.rr.com...
> Hello,
> I have a small workgroup of computers (4) all running windows xp sp2. One
> of them is running msde2000. I can connect to a named instance of sql
> server but can not connect to the default instance. I am using osql and
> using trusted connection. I am not sure what other information is needed
> so please do not hesitate to ask! I am really pulling my hair out on this
> one!
> Any help would be greatly appreciated.
>
|||Are you sure there is a default instance? If it's just MSDE 2000, there
usually is not a default instance. It installs to a named instance by
default. Look in Administrative Tools/Services for all SQL Server services.
See if you have any running besides the named instance.
"Philippe RUELLO" <pruello@.tibco.fr> wrote in message
news:uReN1wF7EHA.2124@.TK2MSFTNGP15.phx.gbl...
> Hello,
> Did you activated TCPIP network protocol for this default instance
(verify[vbcol=seagreen]
> in the error.log for the instance)?
> Hope this will help.
> --
> Philippe RUELLO
> DBA MSSQL
> "Michael O'S" <mroresolutions@.woh.rr.com> a crit dans le message de news:
> NhMxd.3864$mA3.1811@.fe2.columbus.rr.com...
One[vbcol=seagreen]
needed[vbcol=seagreen]
this
>
|||Thank you for your input! I did check tcpip settings and all were good. I
did however find in the server logs that some intances (including the
default) were not listening or failing to bind to port 1433.
I ended up removing msde instance that I could not connect to and
reinstalling making sure to use the DISABLENETWORKPROTOCOLS=0 parameter.
After I did this, I could see all instances of msde on any computer from any
computer in my workgroup.
Good Luck!
"Michael O'S" wrote:

> Hello,
> I have a small workgroup of computers (4) all running windows xp sp2. One
> of them is running msde2000. I can connect to a named instance of sql server
> but can not connect to the default instance. I am using osql and using
> trusted connection. I am not sure what other information is needed so
> please do not hesitate to ask! I am really pulling my hair out on this one!
> Any help would be greatly appreciated.
>
>