Need some advice for best practice approach

[#1]

Hi there,

We are trying to retrieve data from an access database that is located on a different server. We run QV10 on a dedicated win 2008 machine.

I can connect to the database, but only when i make a drive mapping to the server\share with the DB. The MS Access ODBC driver does not support the use of UNC.

Therefor i figured the use of a CMD file in the windows Task Scheduler might be worth a shot.

The contents of the CMD file is this :

----------------------------------------------------------------------

net use P: \\SE94AAPT1\Persmaster$ ******** /USER:********\********   (User and password been asterisked )

"C:\Program Files\QlikView\qv.exe" /r D:\data\QVW\Extractie\K94_Persmaster\Persmaster.qvw

net use P: /delete

----------------------------------------------------------------------

In the QVW we refer to the 32-bit ODBC connection which is present on the server. Becuase this driveer doesnt support UNC we are forced to use a drive letter.

A creative sollution but unfortunatly this doesnt run when scheduled and NOT logged in to the server.

Basicly i am looking for some best practice advice as in How to schedule and retrieve data from an access database on another (non qv) server.

Cheers in advance

Alexandre

[danielrozental]

I'm not quite sure I understand your problem, I have a few questions for you

- Why are you not using QlikView server for the reload?

- Is the drive letter also mapped for the user running the reloads?

[#3]

Hi Daniel,

Thank you for responding to my call of need

- Why are you not using QlikView server for the reload?

The report cannot reload by itself atm because the used ODBC connection cannot use UNC to allocate the access DB.

To go arround this i mapped a drive so i can use that mapped drive ( p: in this case ) in the odbc connection.

When the drive mapping is present ( when i am logged in to the QV server) i can use the server reload. But when i schedule the reload in QV i cannot make the drive mapping.

Therefor i tried using the command line interface.

- Is the drive letter also mapped for the user running the reloads?

The user running the reload is a seperate user and does not have the drive mapping i need for the odbc connection. Hence me trying to script it in the cmd file.

I hope this clears some questions.

Regards Alexandre

[danielrozental]

Can you have that unit mapped for the user running the reloads?

[#5]

Dont know yet, i am going to look into that tomorow with a microsoft system administrator.

I'll get back on it asap

[danielrozental]

Another idea will be to copy the file to a local folder and then open it using the ODBC.

[#7]

Small update :

You can use UNC naming in the OBDC.

How? Just type it in the filename box when you create a new OBDC connection. This is a bit silly imo because the ODBC ui does not have any buttons or something to support this. This can be classified as a small "You need to know" issue.

Now that i can place UNC inside the ODBC i can reload from the QVS Schedule itself

Well, it should be possible but then i stumbled upon another part of my problem, Authorisation is also an issue.

The access on the filesystem is prohibeted because the DB contains data from the personal. Access is granted under strong supervision. I got acces so now i can "see" the DB at least.

To access the data inside the DB i need to use a different user, which has been configured inside the master DB.

Long story so far...hang on we are getting close.

What i learned so far:

1. The authorisation that is checked is from the person logged into the QVS making the ODBC connection.Regardless of the credentials that you can supply configuring the connection because those are used to access the data insde the DB, not the filesystem. So the user setting up AND Using the connection needs to be granted acces on filesystem level.

2. QVS Schedule runs as a service! This service cant use drive mappings nore create them.

3. You can use UNC inside ODBC connections, just type them inside the name field!

My problem was a mixed one. I am working now to get the authorisation correctly configured by our system admins. The UNC inside ODBC is solved so there is no need for drive mappings etc.

Eventhough it isnt solved atm i am sure it will be very soon.

Thanks for the help, it is much appreciated

[#8]

Today i asked about this, and it is something the security officer rather not see