Increasing the session existing time in sql server
-
Hi . I want to know how can i increase the session existence time in SQL server. I have a software that uses a connection to SQL server . when it remains idle for some minutes then the new session will be created to SQL server from the application side . This produces me me a big problem . Because i use the connection_Id for auditing the user . in my login table i have applicationID that refers to connection_Id in sys.dm_exec_connections . when the software authenticates the user , It updates the connection_ID in login table and through this column i can get my user's username in my triggers . here is the problem when my software becomes idle , then another connection_id is created and i can not trace my user because i can not reach my user in logins table this is my login table
CREATE TABLE [dbo].[Logins](
[LoginUser] [nvarchar](50) NOT NULL ,
[Password] [nvarchar](50) NOT NULL,
[IsDefaultPassword] [int] NULL,
[AppID] [uniqueidentifier] NULL,
CONSTRAINT [PK_Logins] PRIMARY KEY CLUSTERED
(
[LoginUser] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]and this is my authenticate procedure
CREATE PROCEDURE [dbo].[Authenticate] (@username nvarchar(30) , @password nvarchar(30))
AS
declare @Authenticated int
set @Authenticated = 0select @Authenticated = 1
from Logins l
where l.LoginUser =@username and l.Password =@passwordif @Authenticated = 1
begin;
declare @AppID uniqueidentifier
select @AppID=p.connection_id from sys.dm_exec_connections p
where p.session_id = @@SPID
update Logins
set AppID = @AppID
where Logins.LoginUser = @username and Logins.Password = @password
end;select @Authenticated ,@AppID
-
Hi . I want to know how can i increase the session existence time in SQL server. I have a software that uses a connection to SQL server . when it remains idle for some minutes then the new session will be created to SQL server from the application side . This produces me me a big problem . Because i use the connection_Id for auditing the user . in my login table i have applicationID that refers to connection_Id in sys.dm_exec_connections . when the software authenticates the user , It updates the connection_ID in login table and through this column i can get my user's username in my triggers . here is the problem when my software becomes idle , then another connection_id is created and i can not trace my user because i can not reach my user in logins table this is my login table
CREATE TABLE [dbo].[Logins](
[LoginUser] [nvarchar](50) NOT NULL ,
[Password] [nvarchar](50) NOT NULL,
[IsDefaultPassword] [int] NULL,
[AppID] [uniqueidentifier] NULL,
CONSTRAINT [PK_Logins] PRIMARY KEY CLUSTERED
(
[LoginUser] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]and this is my authenticate procedure
CREATE PROCEDURE [dbo].[Authenticate] (@username nvarchar(30) , @password nvarchar(30))
AS
declare @Authenticated int
set @Authenticated = 0select @Authenticated = 1
from Logins l
where l.LoginUser =@username and l.Password =@passwordif @Authenticated = 1
begin;
declare @AppID uniqueidentifier
select @AppID=p.connection_id from sys.dm_exec_connections p
where p.session_id = @@SPID
update Logins
set AppID = @AppID
where Logins.LoginUser = @username and Logins.Password = @password
end;select @Authenticated ,@AppID
yousefshokati wrote:
when my software becomes idle , then another connection_id is created
Easiest way out is to not create a new connection, but re-use the existing one. Otherwise, keep the Id around in a variable after your first connect, and pass it as a param on the idle-connection.
Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
-
Hi . I want to know how can i increase the session existence time in SQL server. I have a software that uses a connection to SQL server . when it remains idle for some minutes then the new session will be created to SQL server from the application side . This produces me me a big problem . Because i use the connection_Id for auditing the user . in my login table i have applicationID that refers to connection_Id in sys.dm_exec_connections . when the software authenticates the user , It updates the connection_ID in login table and through this column i can get my user's username in my triggers . here is the problem when my software becomes idle , then another connection_id is created and i can not trace my user because i can not reach my user in logins table this is my login table
CREATE TABLE [dbo].[Logins](
[LoginUser] [nvarchar](50) NOT NULL ,
[Password] [nvarchar](50) NOT NULL,
[IsDefaultPassword] [int] NULL,
[AppID] [uniqueidentifier] NULL,
CONSTRAINT [PK_Logins] PRIMARY KEY CLUSTERED
(
[LoginUser] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]and this is my authenticate procedure
CREATE PROCEDURE [dbo].[Authenticate] (@username nvarchar(30) , @password nvarchar(30))
AS
declare @Authenticated int
set @Authenticated = 0select @Authenticated = 1
from Logins l
where l.LoginUser =@username and l.Password =@passwordif @Authenticated = 1
begin;
declare @AppID uniqueidentifier
select @AppID=p.connection_id from sys.dm_exec_connections p
where p.session_id = @@SPID
update Logins
set AppID = @AppID
where Logins.LoginUser = @username and Logins.Password = @password
end;select @Authenticated ,@AppID
yousefshokati wrote:
This produces me me a big problem
Increasing session timeout so you can track users is unlikely to ever be a good idea. As one example exactly what do you think is going to happen to your system if a 'user' goes on vacation for two weeks but that user remains logged in? Also it appears that you are attempting to manage permissions by user rather than role. And that is going to become a problem with more than a couple of users.