How to Identify Connection

[NeoPa]

I have multiple users using an Access front end plugged into a SQL Server back end.

I want something that identifies the user's session to SQL server.

I've tried @@SPID and found that there can be multiple values when connecting from the same session of Access.

Equally, login and user names/IDs are not reliable as it's perfectly possible to have more than one session of Access going and these need to be treated as separate sessions.

Does anyone have any ideas on how I can identify the current session uniquely?

[ck9663]

I do it the old fashion way. Generate my own Session ID with the user IP, date and time of connection and a newid(). You can pass it from one page to another then do a check to the server if you want the connection to proceed ie. force timeout. I only do it for intranet, though. Users might not want to see I'm grabbing their IP, even if we all know it's actually happening every time you open a page :)

Good Luck!!!

~~ CK

[computerfox]

Would there be a way to generate a random integer for the Access database anytime Access itself loads? Or maybe have a SESSION table and when the user logs in, regardless if it's the same username/password combination, it will create a new record with a randomly generated ID, which would act as the session id?

[computerfox]

@CK That's actually a pretty creative way of generating a session id, but wouldn't that also cause various session id's if there's a lot of open()/close()?

[Rabbit]

You could use the process ID. And on the off chance that the different systems generate the same process ID, combine it with the user name or system name.

[ck9663]

You generate the session ID on the home page. It is then pass through from one page to the other. If you need to direct a user to a specific page, just use the home page on the link, generate the session id, then redirect the user to whichever page you want them to, carrying the session ID with them.

The number of session ID you generate depends on the number of user of your page. Every use is a connection, essentially. You may extend it to track which page is mostly accessed by who and what they did on it, or whatever you need to do :)

~~ CK

[NeoPa]

Thanks for the ideas guys. My preference is strongly towards an automatic system that I don't have to code in by changing things in my project. If possible, I'd like something pre-existing in SQL Server that means something to anyone else who looks at the project without having to decipher the front end (if that makes sense). Essentially something inbuilt and predefined.

@Rabbit.
You refer to a Process ID. How do I access that?

Am I right to interpret your responses as saying that there is nothing you're aware of that provides this functionality as standard?

Any ideas why the Session ID (@@SPID) returns different values when called by the same remote system? This surprised me frankly.

[ck9663]

Could be the way your front-end is retrieving the data from the server. It be opening a session every time.

~~ CK

[Rabbit]

You could grab it from the system processes using WMI. But SQL Server logs it for you in a system table. Between these 4 fields, you should be able to find a combination that works for you.

SELECT session_id, host_name, host_process_id, login_name
FROM sys.dm_exec_sessions

It creates multiple sessions, I presume, so it can run mutliple queries in parallel.

As far as standard functionality, I think this comes down to terminology. This is my understanding of how it works. But I don't have any documentation to back it up so take it with a grain of salt.

As far as SQL Server is concerned, Access is connecting to the database multiple times. So there are multiple connections and multiple sessions. But each of those sessions comes from a certain user and a certain machine. And on those machines, a certain process spawns that connection.

So if you have multiple instances of Access running. Each of those instances of Access gets its own process ID unique to that machine. Each of those instances may connect to SQL Server multiple times. Each instance of Access can be spawned under a different user if they chose to run it under different credentials. So the uniqueness you are looking for could be the combination of login name, process id, and host name. If users are not likely to do so, you can use just process id and login name / host name.

[NeoPa]

I can't say that any of that makes a lot of sense to me :-(

That's not to say I don't appreciate your thoughts on the subject by any means. Simply that I can't see why it would need to create multiple sessions with the caller in order to run processes in parallel. Access has a session open with the SQL Server as I force it to artificially by keeping open a form with a recordset (Dynaset) linked to a table from the SQL database. Why Access would need to open multiple sessions is something one could guess at, but there seems nothing to indicate it. I think I need a better understanding of what's going on and why if I'm to make this work properly.

I'll play with the sys.dm_exec_sessions table and post what I find. I'm really looking for a system variable (@@...) for preference, but if I find something usable in here I'll count this a productive exercise.

Regardless of any results I really appreciate all of your time and efforts helping me on this.

PS. Just caught your last update Rabbit. Ta.

[Rabbit]

If you were to look at the ports on the local machine, you will see that there are multiple ports on the local machine connecting to the SQL Server. I suspect this is so that SQL Server can send asynchronous results to the correct port. This is conjecture again but suppose there are 2 list boxes on a form. I expect that it would send both queries to populate the list boxes to the server, each query on a different port. Access would listen to each of those ports and wait for the data to return. On the SQL Server side, I suspect it stores the port with the session.

[NeoPa]

I'll have to come back to this when I'm a little fresher Rabbit.

What you say makes some sense. How to use that understanding is beyond me at this point in the morning. I'll see what tomorrow brings.

Thanks for all your help :-)

[Rabbit]

Sure thing NeoPa.

You may be interested in the following functions available in SQL Server: SUSER_NAME(), HOST_NAME(), HOST_ID(). They return, respectively, the log in name, the client computer name, and the client process id that created the connection.

If they have 2 instances of Access running on their computer, you can identify them with the HOST_ID().

[NeoPa]

Rabbit:
You may be interested in the following functions available in SQL Server

You can be sure I am my friend. Hopefully they will allow me to put together a reference which is unique to my Access session. I'll have a play.

Even if not then I can always create a unique reference from the Access end and include that in any communications between the two. That just means I have to update all my queries though, which run into many hundreds unfortunately. If I can find a way for SQL Server to determine who I am without my having to tell it explicitly that will be a big bonus.