Hi,
I'm looking for any suggestions/ideas for allowing MS SQL ODBC access for a limited number of users.
This is an new issue since we've recently upgraded our ERP application AND migrated it to a hosted site.
As far as I can tell, we're their first client to ask for ODBC and we're not making too much progress yet.
I'm looking for suggestions, ideas from anyone who might have experienced similar issues.
- ODBC has been traditionally used by a few "super-users" in Excel/Access to perform data analysis/reporting
- and one admin users to build custom views
I've added some of out details in the "references" section below. (I know, it's a lot)!
To anyone who takes the time to read thru all of this...
Thanks in advance,
Bruce
References:
The new "hosted" system:
- Windows Server 2008 R2
- With ERP application that uses an MS SQL database
- ERP users access the system via an RDP client - 2X. Then the fat client can be launched from the 2X interface.
My ODBC testing attempted so far - when logged on to the server locally (RDP)
The Hosting site added a system DSN to the server per our request (we aren't supposed to do anything ourselves). New DSN driver: SQL Server Native Client 10.0, Windows Authentication. When I connect to server (RDP) and run Excel, I can't see the DSN listed as a datasource
I manually made data connection(s) in Excel and returned data.
I used several of the "From Other Sources" options.
----- For example using the Data Connection Wizard
----- Datasource Type: Microsoft SQL Server
-------ServerName: XXXXXXXXX
------- Log On Credentials: Windows Authentication
-------- Database: XXXXXXXXXX (appears to match the system DSN name used)
When I've used ODBC before, everything has always been on the same LAN. (Squirrel, DBVisualizer, Access & Excel)
A database on a hosted site is totally new to me.
The ERP application does have an embedded query tool but it is limited, especially in it's ability to handle complex/aggregate data. One way to get around this is with custom views.
The ERP application query tool can access any custom SQL views we could add.
We're stuck here too.... need ODBC/Windows permissions for an admin account - and software to add those custom views (have used Squirrel and DBVisualizer on the old system).
Bruce O
I'm looking for any suggestions/ideas for allowing MS SQL ODBC access for a limited number of users.
This is an new issue since we've recently upgraded our ERP application AND migrated it to a hosted site.
As far as I can tell, we're their first client to ask for ODBC and we're not making too much progress yet.
I'm looking for suggestions, ideas from anyone who might have experienced similar issues.
- ODBC has been traditionally used by a few "super-users" in Excel/Access to perform data analysis/reporting
- and one admin users to build custom views
I've added some of out details in the "references" section below. (I know, it's a lot)!
To anyone who takes the time to read thru all of this...
Thanks in advance,
Bruce
References:
The new "hosted" system:
- Windows Server 2008 R2
- With ERP application that uses an MS SQL database
- ERP users access the system via an RDP client - 2X. Then the fat client can be launched from the 2X interface.
My ODBC testing attempted so far - when logged on to the server locally (RDP)
The Hosting site added a system DSN to the server per our request (we aren't supposed to do anything ourselves). New DSN driver: SQL Server Native Client 10.0, Windows Authentication. When I connect to server (RDP) and run Excel, I can't see the DSN listed as a datasource
I manually made data connection(s) in Excel and returned data.
I used several of the "From Other Sources" options.
----- For example using the Data Connection Wizard
----- Datasource Type: Microsoft SQL Server
-------ServerName: XXXXXXXXX
------- Log On Credentials: Windows Authentication
-------- Database: XXXXXXXXXX (appears to match the system DSN name used)
When I've used ODBC before, everything has always been on the same LAN. (Squirrel, DBVisualizer, Access & Excel)
A database on a hosted site is totally new to me.
The ERP application does have an embedded query tool but it is limited, especially in it's ability to handle complex/aggregate data. One way to get around this is with custom views.
The ERP application query tool can access any custom SQL views we could add.
We're stuck here too.... need ODBC/Windows permissions for an admin account - and software to add those custom views (have used Squirrel and DBVisualizer on the old system).
Bruce O
