Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MarkWB
Starting Member
8 Posts |
 Posted - 2008-12-16 : 10:55:23
|
| I have been working on a project that involves migrating end users from an MS Access solution that used linked tables in their MDBs to using SSMS.We have done training, supplied documentation and have helped to translate queries.Now we want to stop the use of Access. We don't want to remove the application from their desktop because it is part of the Office suite and doing so would be a big IT undertaking. We don't want to search and destroy MDBs because there is query text that users might need and, generally, that solution is not very elegant.The MDBs have an autoexec macro that uses ADO to link the tables with trusted connections. And all the users have to maintain their trusted connections because they need to connect with SSMS.Watching traces in profiler, I can differentiate the activity by seeing the value of the ApplicationName field, “2007 MS Office System” vs. “SQL Server Management Studio”. So is there a way to deny connections or otherwise stop returning results where the application name is “2007 MS Office System”? Or is there a better way to handle it? |
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 2008-12-16 : 15:45:51
|
| You can kill logins based on the application name rather easily. But I am forced to wonder why you even care that they use SSMS rather than Access.An infinite universe is the ultimate cartesian product. |
 |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-12-18 : 11:29:39
|
| You could run a job to check Application Name and kill connection. Run every 1 minute for a few weeks the users will soon give up.The following will kill spid, but you could just as easily save to permanent table to view worst offenders.select spid ,hostname ,[program_name] ,convert(sysname, rtrim(loginame)) as loginname into #MSAccess from master.dbo.sysprocesses with (nolock)where [program_name]='2007 Microsoft Office system'DECLARE @CMD1 varchar(100)SELECT @CMD1 = 'KILL ' + CAST((Select spid from #MSAccess) AS varchar(5))EXEC (@CMD1) |
|
|
|
MarkWB
Starting Member
8 Posts |
Posted - 2008-12-18 : 11:49:44
|
| Thanks cat, dardusky - I ended up creating this logon trigger:CREATE TRIGGER MSOffice_triggerON ALL SERVER WITH EXECUTE AS 'sa'FOR LOGONASBEGINIF EXISTS(SELECT session_id FROM sys.dm_exec_sessions WHERE session_id = @@spid AND program_name = '2007 Microsoft Office System' AND original_login() NOT IN ('DM\Joe.User')) ROLLBACK;END;I also added a simple exlusions list so we could exclude a small number of users if needed.Turns out that a logon trigger is good in this situation because it happens "inline" with authentication. So basically the trigger is operating before the connection is made. And it is done so in a transaction context. So if you don't want the connection to happen, you just call ROLLBACK.cat, to your question, this is a global company with many many users consuming large amounts of manufacturing data collected in real time. Years ago, when the company was smaller and only had one location, Access linked tables were rolled out as the solution. However, if you consider their current distributed environment and volume of data and look at how ODBC handles data between Access linked tables and SQL Server, you realize how inefficient it is for both server resources and bandwidth as well as the end user experience.That's reason 1. Reason 2 is that with stored procedures and UDFs users can create and store queries on the server to re-use best practices instead of everyone having their own version of MDB files (we have created sandbox DBs for this use with synonyms that point to production data). Reason 3 is that t-sql and the SQL Server platform lifts the ceiling of functionality for advanced users especially in engineering and R & D groups. |
|
|
|
|
|
|
|
|
Managing connections from MS Access