Solved

Host_Name returning wrong computer name

Posted on 2010-08-27
7
498 Views
Last Modified: 2012-05-10
Using SQL Server 2008:  If I run a query from Management Studio on my workstation and just say  select Host_Name() as myPC, then it correctly returns the name of my workstation.
I created a trigger on a table that uses the Host_Name() function when a record in that table is updated. If I connect to SQL server using Microsoft Access, modifiy a record in the table, then the function doesn't return the name of my workstation or even the name of the server that SQL server is hosted on. Instead it returns the name of our Terminal Server.
What's going on here?  How can I correctly identify either the workstation or login name of the user that is modifying a record?  We are not using Windows authentication. We are accessing sql server with Microsoft Access via ODBC connection.
0
Comment
Question by:BradleyCleveland
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 30

Expert Comment

by:Rich Weissler
ID: 33543850
Taking a stab in the dark -- can you confirm that your copy of MS Access is running locally, and not through citrix xenapps or anything?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33544631
>>How can I correctly identify either the workstation or login name of the user that is modifying a record?  <<
Don't rely on HOST_NAME() it is easy to change from the client.  Instead pass in the user name, computer name, IP address of the workstation or whatever.
0
 
LVL 1

Author Comment

by:BradleyCleveland
ID: 33544803
Razmus, I am running Access through my local machine. There are of course other users that are accessing the database with MS Access via TS at the same time I am using my workstation.

ACPerkins: I am trying to isolate on one particular table where a field is being accidentally changed. There are a number of forms that link to this table from the Access program and it is used by dozens of employees. On the main form for editting the data in this table, we trap the username and computer name, but the table is not reflecting this when the data is changed. It's not even supposed to be changed by any other form. There is also a slight possiblity that there is a coding erro in an SP somewhere. So the best way to find the problem is to have SQL server alert me as soon as data is changed in the table. Without being able to identify either the computer name or login name though, Its still going to be difficult to find how the data change was initiated.
0
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.

 
LVL 4

Accepted Solution

by:
MSSystems earned 500 total points
ID: 33545453
I have seen this before with MS Access. You will find in your data source connection, where you can actually specify the workstation name. SQL then takes this as the actual workstation. Please take a look at the data source, I hope this helps
0
 
LVL 1

Author Closing Comment

by:BradleyCleveland
ID: 33546458
Found it. Thanks so much.
0
 
LVL 4

Expert Comment

by:MSSystems
ID: 33546534
It is a pleasure
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33547711
>>You will find in your data source connection, where you can actually specify the workstation name. SQL then takes this as the actual workstation. <<
Yep.  That is exactly what I meant about not relying on HOST_NAME(), it can be spoofed to be anything.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

777 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