Solved

Chnage net_library used by MSSQL/2000

Posted on 2004-03-26
9
335 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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…
Viewers will learn how the fundamental information of how to create a table.

776 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