1

I am familiar with ODBCs by system (system DSNs) and ODBCs by user (user DNSs). I want to have ODBCs by application. I want to limit the ODBC connections available to a specific application, GP 2010. I have multiple GP applications installed on a terminal server (multiple GP directories within Program Files storing multiple Dynamics.exe applications), and I have multiple ODBCs to which to connect from GP 2010.

I want to enforce a rule whereby only specific GP 2010 applications (Dynamics.exe) can launch and connect to specific ODBCs. In other words, I want to impose a rule which creates a 1:1 relationship between:

  1. a GP 2010 application (Dynamics.exe), and
  2. an ODBC (a SQL instance which supports GP)

Environment info:

  • Windows Server 2012 Standard 64-bit
  • SQL 2008 R2 SP2
  • GP 2010 SP3

What I have tried/learned thus far:

I have seen attempts in certain GP environments to use group policy to limit ODBCs available to Windows users via the use of user DSNs. However, such an approach is not helpful where a Windows user legitimately has access to multiple GP applications. When they launch a GP application, they are able to select any ODBC available to their Windows user.

I have experimented with the use of GP 2010 login macros to try and solve this issue. However, this method is insecure and does not scale. Firstly, it assumes that a username and password will be known to the administrator and will usually remain unchanged. If this is the case, the credentials--along with the ODBC--can be embedded into a login macro (which is stored in plain text somewhere). Even still, in order to be used, the login macros must be passed to the GP 2010 shortcut that users launch. In order to be able to specify a generic target path for each GP application, the login macro for each user will have to be stored in a common-per-user location (ex. on their user's desktop). Since this has to be configured per user, this administrative overhead limits scalability.

nairware
  • 309
  • 2
  • 9
  • 17
  • 1
    Why do you want to do this? There's probably another solution to the real problem you're trying to solve. – longneck May 12 '14 at 14:19
  • A GP instances requires (1) a unique GP installation directory and Dynamics.exe application, and (2) a unique SQL instance (not database, I truly mean SQL instance--as in a unique install of SQL). If a user launches a GP application on a server, when they attempt to login, they will be prompted to select to which "Server" (meaning SQL instance/ODBC) they want to connect. – nairware May 12 '14 at 18:19
  • As an administrator, it is easy to lock-down which GP application can be launched by which user. And user DSNs can be specified to limit which users can access which SQL instances/ODBCs/DSNs. However, in the case of a user with access to multiple GP applications and multiple SQL instance (ex. a power user), why should he/she be able to run "GP application A" with "SQL instance B"? I should not be able to do this. GP A should match up with SQL A, GP B with SQL B, and so forth...a 1:1 relationship. – nairware May 12 '14 at 18:21
  • Have you asked Microsoft via your CustomerSource login? You may want to consider doing that in case you run into some issues down the road and then need their help – tegbains May 14 '14 at 08:45
  • @tegbains A few days ago, yes. They are slow to get the ball rolling for this case, but they did tell me yesterday via email that they want to create an advisory case to pursue this (I initally reported via PartnerSource as a break-fix issue). I don't know where MSFT is or what they are doing at this point, but eventually they will probably help. – nairware May 14 '14 at 14:10

2 Answers2

2

I don't think you're going to have much luck getting what you want, because what you're trying to do doesn't jibe with how the Windows security model works.

ODBC DSNs are stored in the registry (either HKEY_LOCAL_MACHINE, for System DSNs, or HKEY_CURRENT_USER, for user DSNs). Permissions can be set on the registry that reference security principals (Users or Groups), but not that reference application software (since application software isn't a security principal). Likewise, the APIs used to access the registry do not have functionality to implement permissions based on the application performing the access. Security is based on users and their group membership, not on the application they're running.

Removing user access to ODBC DSNs won't actually stop the users from accessing the back-end database if they have access. You're just "hiding" the access by obscuring the DSN, not actually securing anything. Security by obscurity isn't really security.

I have no experience with Great Plains, but since it's back-end storage appears to be SQL Server-based, I would think that the Right WayTM of limiting user access to the back-end database would be SQL Server security. A user accessing the SQL Server instance hosting the Great Plains data, assuming you're using Windows authentication on the ODBC DSN, would be "seen" by SQL Server with their Windows user context. You could create SQL Server "Logins" corresponding to the users (or, better still, groups that the users are members of) and limit their access using built-in SQL Server functionality. This seems like a lot better place to limit user access, especially since the database itself will enforce access.

(Not having used Great Plains before, and accepting that it's possible and even likely that the software uses ugly things like SQL Server native authentication. If that's the case, then you just have a mess.)

Evan Anderson
  • 141,071
  • 19
  • 191
  • 328
2

No but if it's that big a deal just run each application in their own VMs under Hyper-V.

Chopper3
  • 100,240
  • 9
  • 106
  • 238