SQL Server - Client HostName in Networking Environment

Hi,
I want to be able to track the client host name in the networking environment. The client host name is used in a trigger for specific tasks.
Windows Authentication is the connection mode applied.
Omer-PitouAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
nishant joshiTechnology Development ConsultantCommented:
To trigger event on specific client name at time of login, you need to create log on  trigger which is best solution for your requirement to track login from client host name.


https://msdn.microsoft.com/en-us/library/bb326598.aspx

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I want to be able to track the client host name in the networking environment.
So, why did you open this question in SQL Server topic?
0
 
Omer-PitouAuthor Commented:
This is about hostname tracked in a sql server trigger. Where do you think it was supposed to be?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
nishant joshiTechnology Development ConsultantCommented:
Hello,

You need to design architecture as below :

create logon trigger which will insert data to table
Another process will keep watch on that table for specific host name.if host name found trigger your process

You can use ssis package to watch on table and execute your custom process.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
This is about hostname tracked in a sql server trigger. Where do you think it was supposed to be?
Sorry but the Networking Environment is confusing me. Can you post the trigger code?
0
 
Omer-PitouAuthor Commented:
Here is the trigger code, every time an update is done from a client host in  a network environment or even locally when not using SSMS for update, the variable @pHostName comes empty.

ALTER TRIGGER [dbo].[contractlog] ON [dbo].[contract]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
      
      DECLARE @pTable VARCHAR(50)='CONTRACT';
      DECLARE @pPrimKey VARCHAR(50)='PKCONTRACT';
      DECLARE @pType CHAR(1);
      DECLARE @pHostName VARCHAR(50)=LEFT(HOST_NAME(),50);
      
      IF OBJECT_ID('tempdb..#tinserted') is not null DROP TABLE dbo.#tinserted;
      IF OBJECT_ID('tempdb..#tdeleted') is not null DROP TABLE dbo.#tdeleted;
      
      SELECT * INTO dbo.#tinserted FROM inserted;
      
      SELECT * INTO dbo.#tdeleted FROM deleted;            
            
      IF exists (SELECT * FROM inserted)
            IF exists (SELECT * FROM deleted)
                  SET @pType = 'U';                  
            ELSE
                  SET @pType = 'I';
      ELSE
            SET @pType = 'D';      
      
      EXEC dbo.trackchanges @pTable, @pPrimKey, @pType, @pHostName; /*Syntax tableName, primaryKeyField, TriggerType*/      
     
END
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
There's no reason to be empty inside the trigger.
What about the trackchanges procedure? How it is defined?
0
 
Omer-PitouAuthor Commented:
trackChanges is a stored procedure, and the @pHostName is one of the parameters sent to that stored procedure.
When I update the table from SSMS, the hostname comes with a value, while it is empty when the update is done from my application.
0
 
nishant joshiTechnology Development ConsultantCommented:
You need to change application code to update host name while updating record.

Another solution is add trigger to table which will update host name for update command.
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Host name should always exists since it's the name of the database server.
Or something is missing in the posted code or something very weird is happening. Unfortunaly I can't see what.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.