April 26, 2015 at 9:01 pm
Hi everyone,
Oracle and Teradata are able to capture the windows/client login that connects to their database with a functionnal ID.
I've searched around and I don't think MS SQL can do this...
I administer SQL 2014 server and also SAS Enterprise guide product. I've created sql server logins for several teams so everyone shares the same sql server login which is used to bind the SAS library to my SQL server database.
However, when looking at activity monitor, I can't drill down to which user is executing a particular query. I only see the SQL login which is share amongs 200 other folks...
On the other hand, when they connect from SAS to Oracle or Teradata, I can see the shared login + additionally their windows NT login used to connect to SAS which is awesome....they can't fool me by using a shared/funcionnal ID....i still see who's using that ID....
Thanks
April 26, 2015 at 9:39 pm
There is no connection made between the login made in the connection to SQL Server to the Windows Login that may have initiated it from the client end. This is why security standards state to not used shared accounts with SQL Server. If an environment desires to use shared accounts the only thing you can do is map the connection to the host machine (client_net_address) via sys.dm_exec_connections.
I would expect this works in Oracle and Teradata because the client end is sending that information to the database/system. In SQL Server that is outside the context of the connection itself.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 27, 2015 at 3:08 am
You can include this information in the connection strings from the application and then it will be available in SQL Server. But, if it's not a part of the connection string, it just won't be there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply