Solved

Chnage net_library used by MSSQL/2000

Posted on 2004-03-26
9
329 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
  • 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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 250 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

706 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now