Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Chnage net_library used by MSSQL/2000

Posted on 2004-03-26
9
Medium Priority
?
344 Views
Last Modified: 2006-11-17
I have two servers. One local and one remote. Server Network Utility shows both have Named Pipes and TCP/IP enabled in that order and on the General Tab and both are listed under the Network Libraries in the same order.

My problem is this. The remote server shows Named Pipes as the net_library in the sysprocesses table and the local server shows TCP/IP. I need to change the local server to Named Pipes in order to retrieve the nt_username in the sysprocesses table.

What do I need to do in order to make the change from TCP/IP to Named Pipes. I can see no difference in the configuration between the two servers other than the net_library used.

I am running NT4.0sp6a and MSSQL 2000sp2 on bother servers.

Thank you,

Howard Bock
Bock and Bock
Dallas, TX
0
Comment
Question by:hmbtx
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
9 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 10688461
Bad idea to use the sysprocesses table.  system_user won't return what you need.

Are you connecting with an application (you can change the default prot, but the app could override it).

Here is the MS article on changing the default prot:

http://support.microsoft.com/default.aspx?scid=kb%3BEN-US%3B250550

Brett
0
 

Author Comment

by:hmbtx
ID: 10688547
Brett:

The document you pointed me to said "The usual procedure to do this is to use the Client Network Utility to change this setting." Can you tell me how I can make the change with Client Network Utility?

Also, you said that it was a bad idea to use the sysprocesses table. Where else can I retrieve the value of the nt_username?

My problem is that I have a need to determine what NT User is accessing a particular database. The nt_username from sysprocesses returns this information from one server but not from another.

Thanks for your help.

Howard
0
 
LVL 34

Expert Comment

by:arbert
ID: 10688741
Is the other server, by chance, using Mixed or SQL Server authentication?  If so, nt_username won't show up....What is the end result you're looking for--how are you "selecting this data" and what do you want to do with it.


In the client network utility, you can use the "up/down" arrows to raise the priority of the protocol or you can enable/disable a protocol, but you can't really set a default.  You can also set the priority of the prots on the server..
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:hmbtx
ID: 10689043
On one of my servers the following query gives me the Database Name followed by the Computer Name and NT User Name that has the database opened. (My second server will not return the nt_username).

select sysdatabases.name,sysprocesses.hostname,sysprocesses.nt_username
from sysprocesses
inner join sysdatabases
      on sysprocesses.dbid=sysdatabases.dbid
where sysprocesses.hostname <> '' and sysdatabases.name like 'Membership%'
order by sysprocesses.hostname

Both servers are using Mixed or SQL Server authentication. Both servers look identical in Network Client Utilities. That is, under the General Tab, Enabled protocols by order: Named Pipes, TCP/IP. Under the Network Libraries Tab, Names Pipes is first followed by TCP/IP.

However, there is one difference between the two servers. The one that returns the nt_username shows Names Pipes as the net_library and the server that does not return the nt_username shows TCP/IP as the net_library.

I am fairly certain that if I can change my server from using the TCP/IP net_library to Named Pipes I will be able to retrieve the nt_username.

I just can’t figure out how to change the net_library in Client Network Utility as I prefer not to go to the registry to make this change.

Also, what do you mean by "You can also set the priority of the prots on the server.."?

Thanks,

Howard
0
 
LVL 34

Expert Comment

by:arbert
ID: 10710832
You can set the priority of the protocol, but you can set a default protocol without disabling the ones you don't want (there by making a default).

I would be really leary of building  a process off of the sysprocesses table--it's very likely to change (and has with the last couple of SQL Service packs) and will be totally different with the release of Yukon.  I take it you can't control the front-end application to pass the workstation/user correctly?
0
 

Author Comment

by:hmbtx
ID: 10713258
Can you tell me how to set the default protocol without disabling the ones I don' want (there by making a default)?

Also, how would you suggest I get the nt_username if I don't use the sysprocesses table?

I am not quite sure what you mean by "can't control the front-end application to pass the workstation/user correctly?

Howard
0
 
LVL 34

Accepted Solution

by:
arbert earned 1000 total points
ID: 10729350
Sorry, should have been "can you control the front-end application to pass the workstation/user correctly"


Um, that link I posted above to microsoft tells you how to edit the registry to change the default protocol...
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question