Post reply

identify applications using sa

  • 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

  • September 26, 2011 at 11:14 pm

    #1386921

    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

  • September 27, 2011 at 12:45 am

    #1386946

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

    CEWII

  • September 27, 2011 at 1:25 am

    #1386954

    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.

  • September 28, 2011 at 1:03 pm

    #1388030

    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

  • September 28, 2011 at 1:15 pm

    #1388034

    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

  • September 28, 2011 at 2:15 pm

    #1388072

    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

  • September 28, 2011 at 2:28 pm

    #1388082

    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!

  • September 28, 2011 at 3:03 pm

    #1388115

    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

  • September 28, 2011 at 4:24 pm

    #1388141

    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!

  • September 29, 2011 at 9:15 am

    #1388442

    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

  • September 29, 2011 at 10:07 am

    #1388482

    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!

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

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