Solved

Chnage net_library used by MSSQL/2000

Posted on 2004-03-26
9
339 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
Industry Leaders: 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!

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Syntax issue with my Where Clause SQL 2012 20 38
SQL Sum of items in two tables not equal. 5 43
SSIS GUID Variable 2 30
SQL Add Parameter in Variable 4 21
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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

685 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