identify applications using sa

Question

identify applications using sa

Pyman

SSChampion

Points: 13830
More actions
September 26, 2011 at 10:25 pm

#390479

Is there a way that we can identify how many applications are using sa username and password in the connection string
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

You could use a logon trigger that logs the connection to a table.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

Or a login audit or a trace of the login events.. several things are possible..

CEWII

CoolCodeShare SSCrazy Points: 2584 More actions · Answer 3

Or you may also try these:

select * from sys.sysprocesses

EXEC sp_who

EXEC sp_who2

Please see there are ProgramName, HostName columns to help you up to some extents.

Pyman SSChampion Points: 13830 More actions · Answer 4

Thanks much for your responses...I was actually looking to identify if there is a way I can capture the connection strings of those applications when they hit the database 😀 ...any inputs are greatly appreciated..

thanks

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

To capture the actual connection string you would need a packet capture.

To get all the relevant information for the connection, try querying sys.dm_exec_sessions

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Pyman SSChampion Points: 13830 More actions · Answer 6

Thank you...i will try and get back to you

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Lowell SSC Guru Points: 323518 More actions · Answer 7

any chance you can change the sa password, and then wait for someone to report which apps are failing?

yeah i know you can't do that....

for a similar issue, i used this proc below...it's just logging sp_who2 results...but running this every 5 minutes or so, eventually you can run this:

SELECT DISTINCT [HOSTNAME],[PROGRAMNAME],[LOGIN] from WHO_DATA WHERE [LOGIN]='sa'

that will help you find the machine and application name that is using sa.

CREATE PROCEDURE PR_CAPTURESP_WHO

AS

BEGIN

SET NOCOUNT ON

IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'WHO_DATA') AND xtype in (N'U'))

CREATE TABLE WHO_DATA (

[WHORESULTSID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

[SPID] CHAR (5) NULL,

[INSERTDT] DATETIME NULL DEFAULT(GETDATE()),

[STATUS] VARCHAR(30) NULL,

[LOGIN] VARCHAR(30) NULL,

[HOSTNAME] VARCHAR(30) NULL,

[BLKBY] VARCHAR(30) NULL,

[DBNAME] VARCHAR(30) NULL,

[COMMAND] VARCHAR(30) NULL,

[CPUTIME] INT NULL,

[DISKIO] INT NULL,

[LASTBATCH] VARCHAR(30) NULL,

[PROGRAMNAME] VARCHAR(200) NULL,

[SPIDINT] INT NULL,

[REQUESTID] INT NULL

)

--table exists, insert some data

INSERT INTO WHO_DATA(SPID,Status,Login,HostName,BlkBy,DBName,Command,CPUTime,DiskIO,LastBatch,ProgramName,SPIDINT,REQUESTID)

EXEC sp_who2

--don't care about spids less than 50 anyway:

DELETE FROM WHO_DATA WHERE SPIDINT < 50

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Pyman SSChampion Points: 13830 More actions · Answer 8

Thanks Lowell...I am able to see from a host ".Net SqlClient Data Provider" as the program name logged multiple times...so is that the actual application?

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Lowell SSC Guru Points: 323518 More actions · Answer 9

that could be more than one program, written in .net, connecting to your database; it might be the same program from multiple machines; that is what a SQLConnection will call itself unless a connection string is built that features "Application Name" in the connection string

Private Const SqlConnectionFormat As String = "

data source={0};

initial catalog={1};

user id={2};

password={3};

Trusted_Connection=False;

Workstation ID=Ghost in The Machine;

Application Name=HaxxorPadPlusPlus;"

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Pyman SSChampion Points: 13830 More actions · Answer 10

Is there a way to find the actual executable that is using the sql authentication in connection strings.

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Lowell SSC Guru Points: 323518 More actions · Answer 11

no...from what we posted above, i think it's now detecitve work...

@ 12 noon, the machine \\BIZ100 was connected using sa
we find that machine, and find Bob sits at the desk in front of that machine.
...Bob, what program were you using that connected to the database at that time?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!