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
Solved

Chnage net_library used by MSSQL/2000

Posted on 2004-03-26
9
338 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your 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 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

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.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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 video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

809 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