We are moving an Access2000 adp application to Access 2003. Access2000
version uses approle for security but we found Approle does not work the
same in Access2003 so we are switching to a proxy security method.
Problem
We need a way for SQL to know the nt username that initiated the proxy
connection. We need SQL to be able to retrieve that username very, very
quickly (basically without a table lookup) for each user's spids, regardless
of how many connections Access decides to make for the adp.
Details:
a.. All the insert and update triggers depended on SYSTEM_USER to stamp
the user who wrote the data on the row
b.. Many stored procedures and views use a UDF that also depends on
SYSTEM_USER
c.. Since we're reconnecting each user as a proxy user, we need a way for
SQL to determine the nt user of the application.
d.. Note: Modifying the client to pass the user in is simply not practical
due to the huge amount of code change.
Our solution
We are using SET CONTEXT_INFO to stuff the user's name into the context_info
of sysprocesses when the app first logs in. We have a UDF - fnSystemUser
that queries sysprocesses to return what SYSTEM_USER used to. Unfortunately,
for the triggers or any UDFs (which previously only hit SYSTEM_USER), that
meant a nose dive in performance since the UDF is executing on every row
instead of evaluating once as if it were deterministic for the life of that
query. Although SYSTEM_USER is nondeterministic, it appears it was only
evaluated once for the queries instead of the performance we see now on our
UDF returning the system user name from context_info, that indicates
executing on every row.
Since Access adps open multiple connections dynamically our fnSystemUser is
coded to find the Context_info of the spid that was first set through client
code. See the code below:
CREATE FUNCTION dbo.fnSystemUser()
RETURNS nvarchar(50)
AS
BEGIN
DECLARE @.ContextInfo varbinary(128)
DECLARE @.DomainUserName nchar(128)
-- First attempt to get the username stuffed into
-- context_info of this connection
SELECT @.ContextInfo = context_info
FROM master..sysprocesses
WHERE spid = @.@.SPID
-- Convert it to nvarchar
SET @.DomainUserName = CAST(CAST(REPLACE(@.ContextInfo,0x0000,''
) AS
varbinary(128)) AS nvarchar(50))
-- If the context info is blank then we're on one of the connections
-- that Access dynamically created but the client code can't access
to stuff
-- something into the context_info. Interrogate all other spids for
this
-- user and this client process (using net_address to guarantee we've
got the
-- right user), to pull username from the context info of another
connection/spid
IF LEN(@.DomainUserName) < 1
BEGIN
DECLARE @.NetAddress nchar(12)
SELECT TOP 1 @.NetAddress = net_address
FROM master..sysprocesses p
WHERE SPID = @.@.SPID
SELECT TOP 1 @.ContextInfo = context_info
FROM master..sysprocesses p
INNER JOIN master..sysdatabases d
on p.dbid = d.dbid
WHERE hostprocess = HOST_ID()
AND d.Name = DB_NAME()
AND NOT CAST(context_info as nchar(50))= SPACE(50)
AND (LEN(@.NetAddress) < 1 OR net_address = @.NetAddress)
ORDER BY Context_info
SET @.DomainUserName =
CAST(CAST(REPLACE(@.ContextInfo,0x0000,''
) AS varbinary(128)) AS
nvarchar(50))
END
/* Fail safe, if we couldn't find a non-empty Context_Info to
discover the
logged in user then could be the user is attached with Query
Analyzer or
some other method and we'll just use the user as they are logged
into SQL */
IF LEN(@.DomainUserName) < 1
SET @.DomainUserName = SYSTEM_USER
RETURN
SUBSTRING(@.DomainUserName,CHARINDEX(N''
,@.DomainUserName)+1,LEN(@.DomainUserN
ame)-(CHARINDEX(N'',@.DomainUserName)))
END
GO
Solving the performance problem
Here are some workaround we've tried:
a.. Querying sysprocesses seems slow so we instead created a table
SyUserLogin and use HostID() and HostName() to find the right row. We've
seen SQL change HostName (or Workstation ID) to "Pool08" and seen 2 users
have the same HostName. So there is no guarantee that when a user logs in
that HostID() and HostName() will uniquely identify each user. but the odds
are in our favor.
Basically here's what runs (stripped of the uniqueness checks) when the
user first logs in to SQL:
INSERT INTO [dbo].[SyUserLogin]
([UserName], [HostName], [HostID], [NetAddress])
SELECT @.SystemUserName, -- username that we pass in
Host_Name(),
Host_id(),
@.NetAddress -- from the sysprocesses row for this spid
Here's the new fnSystemUser:
CREATE FUNCTION dbo.fnSystemUser()
RETURNS nvarchar(50)
AS
BEGIN
RETURN
(SELECT UpdateUserName
FROM SyUserLogin
WHERE HostName = Host_NAME()
AND HostID = HOST_ID()
)
END
a.. We tried to change the connection string to use either "Application
Name" or "Workstation ID" as a cubby hole to stuff the nt user name. We can
get this to work in a VB Script:
dim objConnection
dim strAccessConnect
strAccessConnect = "Provider=SQLOLEDB.1" & _
";Net=dbnmpntw;Data Source=ASQLServer" & _
";Initial Catalog=MyDatabase" & _
";Persist Security Info=False;Application
Name=MyTestApp;Workstation ID=NTUserName;"
Set objConnection = createobject("ADODB.Connection")
objConnection.Open strAccessConnect, "OurProxy", "ProxyPassword"
Msgbox objConnection.ConnectionString
objconnection.close
set objconnection = nothing
But when we try this in Access2003, Access seems to stomp on those value
when it connects. We've played with the Connection dialog settings, but
can't seem to convince Access to leave our connection string values alone.
If we could get this to work then it would be ideal. Querying HostName() or
even Appname() although obscure would be as fast as SYSTEM_USER()
a.. Could we somehow create a function that would wrap context_info and
match the speed of SYSTEM_USER?
Thank you in advance for reading through this and commenting!
Kimberley Yochum kyochum@.wthq.comi dont want to sound dumb, but you've tried SUSER_SNAME() and all the other
ones, right'
also, if you're scanning against a large list of Text data, you might want
to consider using HASH INDEXES (aka the checksum function) in order to speed
this.
"Kimberley Yochum" <kyochum@.bigzoo.net> wrote in message
news:%23BOvPMX0EHA.632@.TK2MSFTNGP10.phx.gbl...
> We are moving an Access2000 adp application to Access 2003. Access2000
> version uses approle for security but we found Approle does not work the
> same in Access2003 so we are switching to a proxy security method.
> Problem
> We need a way for SQL to know the nt username that initiated the proxy
> connection. We need SQL to be able to retrieve that username very, very
> quickly (basically without a table lookup) for each user's spids,
regardless
> of how many connections Access decides to make for the adp.
> Details:
> a.. All the insert and update triggers depended on SYSTEM_USER to stamp
> the user who wrote the data on the row
> b.. Many stored procedures and views use a UDF that also depends on
> SYSTEM_USER
> c.. Since we're reconnecting each user as a proxy user, we need a way
for
> SQL to determine the nt user of the application.
> d.. Note: Modifying the client to pass the user in is simply not
practical
> due to the huge amount of code change.
> Our solution
> We are using SET CONTEXT_INFO to stuff the user's name into the
context_info
> of sysprocesses when the app first logs in. We have a UDF - fnSystemUser
> that queries sysprocesses to return what SYSTEM_USER used to.
Unfortunately,
> for the triggers or any UDFs (which previously only hit SYSTEM_USER), that
> meant a nose dive in performance since the UDF is executing on every row
> instead of evaluating once as if it were deterministic for the life of
that
> query. Although SYSTEM_USER is nondeterministic, it appears it was only
> evaluated once for the queries instead of the performance we see now on
our
> UDF returning the system user name from context_info, that indicates
> executing on every row.
> Since Access adps open multiple connections dynamically our fnSystemUser
is
> coded to find the Context_info of the spid that was first set through
client
> code. See the code below:
>
> CREATE FUNCTION dbo.fnSystemUser()
> RETURNS nvarchar(50)
> AS
> BEGIN
> DECLARE @.ContextInfo varbinary(128)
> DECLARE @.DomainUserName nchar(128)
> -- First attempt to get the username stuffed into
> -- context_info of this connection
> SELECT @.ContextInfo = context_info
> FROM master..sysprocesses
> WHERE spid = @.@.SPID
> -- Convert it to nvarchar
> SET @.DomainUserName = CAST(CAST(REPLACE(@.ContextInfo,0x0000,''
) AS
> varbinary(128)) AS nvarchar(50))
> -- If the context info is blank then we're on one of the
connections
> -- that Access dynamically created but the client code can't access
> to stuff
> -- something into the context_info. Interrogate all other spids
for
> this
> -- user and this client process (using net_address to guarantee
we've
> got the
> -- right user), to pull username from the context info of another
> connection/spid
> IF LEN(@.DomainUserName) < 1
> BEGIN
> DECLARE @.NetAddress nchar(12)
> SELECT TOP 1 @.NetAddress = net_address
> FROM master..sysprocesses p
> WHERE SPID = @.@.SPID
> SELECT TOP 1 @.ContextInfo = context_info
> FROM master..sysprocesses p
> INNER JOIN master..sysdatabases d
> on p.dbid = d.dbid
> WHERE hostprocess = HOST_ID()
> AND d.Name = DB_NAME()
> AND NOT CAST(context_info as nchar(50))= SPACE(50)
> AND (LEN(@.NetAddress) < 1 OR net_address = @.NetAddress)
> ORDER BY Context_info
> SET @.DomainUserName =
> CAST(CAST(REPLACE(@.ContextInfo,0x0000,''
) AS varbinary(128)) AS
> nvarchar(50))
> END
> /* Fail safe, if we couldn't find a non-empty Context_Info to
> discover the
> logged in user then could be the user is attached with Query
> Analyzer or
> some other method and we'll just use the user as they are logged
> into SQL */
> IF LEN(@.DomainUserName) < 1
> SET @.DomainUserName = SYSTEM_USER
> RETURN
>
SUBSTRING(@.DomainUserName,CHARINDEX(N''
,@.DomainUserName)+1,LEN(@.DomainUserN[vbc
ol=seagreen]
> ame)-(CHARINDEX(N'',@.DomainUserName)))
> END
> GO
> Solving the performance problem
> Here are some workaround we've tried:
> a.. Querying sysprocesses seems slow so we instead created a table
> SyUserLogin and use HostID() and HostName() to find the right row. We've
> seen SQL change HostName (or Workstation ID) to "Pool08" and seen 2 users
> have the same HostName. So there is no guarantee that when a user logs in
> that HostID() and HostName() will uniquely identify each user. but the[/vbcol]
odds
> are in our favor.
> Basically here's what runs (stripped of the uniqueness checks) when the
> user first logs in to SQL:
> INSERT INTO [dbo].[SyUserLogin]
> ([UserName], [HostName], [HostID], [NetAddress]
)
> SELECT @.SystemUserName, -- username that we pass in
> Host_Name(),
> Host_id(),
> @.NetAddress -- from the sysprocesses row for this spid
> Here's the new fnSystemUser:
> CREATE FUNCTION dbo.fnSystemUser()
> RETURNS nvarchar(50)
> AS
> BEGIN
> RETURN
> (SELECT UpdateUserName
> FROM SyUserLogin
> WHERE HostName = Host_NAME()
> AND HostID = HOST_ID()
> )
> END
>
> a.. We tried to change the connection string to use either "Application
> Name" or "Workstation ID" as a cubby hole to stuff the nt user name. We
can
> get this to work in a VB Script:
> dim objConnection
> dim strAccessConnect
> strAccessConnect = "Provider=SQLOLEDB.1" & _
> ";Net=dbnmpntw;Data Source=ASQLServer" & _
> ";Initial Catalog=MyDatabase" & _
> ";Persist Security Info=False;Application
> Name=MyTestApp;Workstation ID=NTUserName;"
> Set objConnection = createobject("ADODB.Connection")
> objConnection.Open strAccessConnect, "OurProxy", "ProxyPassword"
> Msgbox objConnection.ConnectionString
> objconnection.close
> set objconnection = nothing
>
> But when we try this in Access2003, Access seems to stomp on those value
> when it connects. We've played with the Connection dialog settings, but
> can't seem to convince Access to leave our connection string values alone.
> If we could get this to work then it would be ideal. Querying HostName()
or
> even Appname() although obscure would be as fast as SYSTEM_USER()
> a.. Could we somehow create a function that would wrap context_info and
> match the speed of SYSTEM_USER?
> Thank you in advance for reading through this and commenting!
> Kimberley Yochum kyochum@.wthq.com
>
>|||Thank you Aaron, but all the user functions (System_user, current_user,
session_user, user, user_name, suser_sname, etc.) return the Proxy user name
once a SQL user login has been performed.
Still hoping for some understanding about what Access is doing to my
connection string values or suggestions on what we can do in SQL...
Thank you in advance.
"aaron kempf" <aarkem@.safeco.com> wrote in message
news:OF9GDhZ0EHA.2228@.TK2MSFTNGP15.phx.gbl...
> i dont want to sound dumb, but you've tried SUSER_SNAME() and all the
other
> ones, right'
> also, if you're scanning against a large list of Text data, you might want
> to consider using HASH INDEXES (aka the checksum function) in order to
speed
> this.
>
>
> "Kimberley Yochum" <kyochum@.bigzoo.net> wrote in message
> news:%23BOvPMX0EHA.632@.TK2MSFTNGP10.phx.gbl...
> regardless
stamp[vbcol=seagreen]
> for
> practical
> context_info
> Unfortunately,
that[vbcol=seagreen]
> that
> our
> is
> client
AS[vbcol=seagreen]
> connections
access[vbcol=seagreen]
> for
> we've
logged[vbcol=seagreen]
>
SUBSTRING(@.DomainUserName,CHARINDEX(N''
,@.DomainUserName)+1,LEN(@.DomainUserN[vbc
ol=seagreen]
users
in[vbcol=seagreen]
> odds
the[vbcol=seagreen]
"Application[vbcol=seagreen]
> can
alone.[vbcol=seagreen]
> or
and[vbcol=seagreen]
>|||can you use the application name value instead?
"Kimberley Yochum" <kyochum@.wthq.com> wrote in message
news:%23rCee%23Z0EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Thank you Aaron, but all the user functions (System_user, current_user,
> session_user, user, user_name, suser_sname, etc.) return the Proxy user
name
> once a SQL user login has been performed.
> Still hoping for some understanding about what Access is doing to my
> connection string values or suggestions on what we can do in SQL...
> Thank you in advance.
> "aaron kempf" <aarkem@.safeco.com> wrote in message
> news:OF9GDhZ0EHA.2228@.TK2MSFTNGP15.phx.gbl...
> other
want[vbcol=seagreen]
> speed
the[vbcol=seagreen]
very[vbcol=seagreen]
> stamp
way[vbcol=seagreen]
fnSystemUser[vbcol=seagreen]
> that
row[vbcol=seagreen]
only[vbcol=seagreen]
on[vbcol=seagreen]
fnSystemUser[vbcol=seagreen]
> AS
> access
spids[vbcol=seagreen]
another[vbcol=seagreen]
@.NetAddress)[vbcol=seagreen]
> logged
>
SUBSTRING(@.DomainUserName,CHARINDEX(N''
,@.DomainUserName)+1,LEN(@.DomainUserN[vbc
ol=seagreen]
We've
> users
> in
> the
> "Application
We[vbcol=seagreen]
_[vbcol=seagreen]
value[vbcol=seagreen]
but[vbcol=seagreen]
> alone.
HostName()[vbcol=seagreen]
> and
>|||Yes I would love to but all my attempts to set an App name in the connection
string are being overwritten by Access.
Here's the connection string I tried. When I tried this in a VB script, it
worked fine and sysprocess showed the appname "MyTestApp" and the hostname
"NTUserName". But when I programmatically set the Access
CurrentProject.Connection by opening with the same connection string, those
value get tossed.
strAccessConnect = "Provider=SQLOLEDB.1" & _
";Net=dbnmpntw;Data Source=ASQLServer"
& _
";Initial Catalog=MyDatabase" & _
";Persist Security
Info=False;Application Name=MyTestApp;Workstation ID=NTUserName;"
Any suggestions? Have you had any success in setting this before? I'd love
to hear about it!!
"david epsom dot com dot au" <david@.epsomdotcomdotau> wrote in message
news:%23sSxhz30EHA.1740@.TK2MSFTNGP15.phx.gbl...
> can you use the application name value instead?
> "Kimberley Yochum" <kyochum@.wthq.com> wrote in message
> news:%23rCee%23Z0EHA.1404@.TK2MSFTNGP11.phx.gbl...
> name
> want
Access2000[vbcol=seagreen]
> the
proxy[vbcol=seagreen]
> very
on[vbcol=seagreen]
> way
> fnSystemUser
SYSTEM_USER),[vbcol=seagreen]
> row
of[vbcol=seagreen]
> only
> on
> fnSystemUser
through[vbcol=seagreen]
CAST(CAST(REPLACE(@.ContextInfo,0x0000,''
)[vbcol=seagreen]
> spids
guarantee[vbcol=seagreen]
> another
> @.NetAddress)
Query[vbcol=seagreen]
>
SUBSTRING(@.DomainUserName,CHARINDEX(N''
,@.DomainUserName)+1,LEN(@.DomainUserN[vbc
ol=seagreen]
> We've
logs
the[vbcol=seagreen]
when[vbcol=seagreen]
> We
&[vbcol=seagreen]
> _
Info=False;Application[vbcol=seagreen]
> value
> but
> HostName()
context_info[vbcol=seagreen]
>|||> string are being overwritten by Access.
rats. :~(
I'm an Access person, and I don't know anything else you could
try on the Access side. I guess you will be looking for a way
to cache the value, so that fnSystemUser doesn't need to do
SELECT query on every call, but I don't know what the options
are in SQL Server.
(david)
"Kimberley Yochum" <kyochum@.wthq.com> wrote in message
news:OhSjrRA1EHA.3448@.TK2MSFTNGP09.phx.gbl...
> Yes I would love to but all my attempts to set an App name in the
connection
> string are being overwritten by Access.
> Here's the connection string I tried. When I tried this in a VB script,
it
> worked fine and sysprocess showed the appname "MyTestApp" and the hostname
> "NTUserName". But when I programmatically set the Access
> CurrentProject.Connection by opening with the same connection string,
those
> value get tossed.
> strAccessConnect = "Provider=SQLOLEDB.1" & _
> ";Net=dbnmpntw;Data
Source=ASQLServer"
> & _
> ";Initial Catalog=MyDatabase" & _
> ";Persist Security
> Info=False;Application Name=MyTestApp;Workstation ID=NTUserName;"
>
> Any suggestions? Have you had any success in setting this before? I'd
love
> to hear about it!!
> "david epsom dot com dot au" <david@.epsomdotcomdotau> wrote in message
> news:%23sSxhz30EHA.1740@.TK2MSFTNGP15.phx.gbl...
current_user,[vbcol=seagreen]
user[vbcol=seagreen]
the[vbcol=seagreen]
might[vbcol=seagreen]
to[vbcol=seagreen]
> Access2000
work[vbcol=seagreen]
> proxy
to[vbcol=seagreen]
> on
a[vbcol=seagreen]
> SYSTEM_USER),
every[vbcol=seagreen]
life[vbcol=seagreen]
> of
now[vbcol=seagreen]
indicates[vbcol=seagreen]
> through
> CAST(CAST(REPLACE(@.ContextInfo,0x0000,''
)
can't[vbcol=seagreen]
> guarantee
SPACE(50)[vbcol=seagreen]
to[vbcol=seagreen]
> Query
are[vbcol=seagreen]
>
SUBSTRING(@.DomainUserName,CHARINDEX(N''
,@.DomainUserName)+1,LEN(@.DomainUserN[vbc
ol=seagreen]
table
2[vbcol=seagreen]
> logs
> the
> when
name.[vbcol=seagreen]
Source=ASQLServer"[vbcol=seagreen]
> &
> Info=False;Application
settings,[vbcol=seagreen]
values[vbcol=seagreen]
> context_info
>|||try with current_user() or user_name()
"david epsom dot com dot au" <david@.epsomdotcomdotau> wrote in message
news:OHiFPga1EHA.2568@.TK2MSFTNGP11.phx.gbl...
> rats. :~(
> I'm an Access person, and I don't know anything else you could
> try on the Access side. I guess you will be looking for a way
> to cache the value, so that fnSystemUser doesn't need to do
> SELECT query on every call, but I don't know what the options
> are in SQL Server.
> (david)
>
> "Kimberley Yochum" <kyochum@.wthq.com> wrote in message
> news:OhSjrRA1EHA.3448@.TK2MSFTNGP09.phx.gbl...
> connection
> it
hostname[vbcol=seagreen]
> those
> Source=ASQLServer"
> love
> current_user,
> user
> the
> might
order[vbcol=seagreen]
> to
> work
method.[vbcol=seagreen]
very,[vbcol=seagreen]
spids,[vbcol=seagreen]
> to
depends[vbcol=seagreen]
need[vbcol=seagreen]
> a
not[vbcol=seagreen]
> every
> life
was[vbcol=seagreen]
> now
> indicates
> can't
other[vbcol=seagreen]
> SPACE(50)
Context_Info[vbcol=seagreen]
> to
> are
>
SUBSTRING(@.DomainUserName,CHARINDEX(N''
,@.DomainUserName)+1,LEN(@.DomainUserN[vbc
ol=seagreen]
> table
row.
seen[vbcol=seagreen]
> 2
user[vbcol=seagreen]
but[vbcol=seagreen]
spid[vbcol=seagreen]
> name.
> Source=ASQLServer"
those[vbcol=seagreen]
> settings,
> values
SYSTEM_USER()[vbcol=seagreen]
>
sql
