IT Side of Data Connection

[#1]

Hi

I have been searching and googling, but cannot find the answer to my question.

I have used QlikView as a developer, so know how to connect to a data source that I have set up myself.

My question is, what needs to happen on the IT side, or the server side, to enable a connection to a database (e.g. SQL Server) datasource?

In the past I have merely asked an IT manager to sort this out, but now I am the IT guy!

Thanks in advance

Simon

[Miguel_Angel_Baeyens · Accepted solution]

Hi Simon,

The only thing you need is a valid username and password to log into the database and read data (perform SQL SELECT statements). Once you have that, you need to create a System DSN or OLE DB (if you are using SQL Server, the latter probably will work better) from the Windows button, Control Panel, Administrative Tools, Data Sources.

Hope that helps.

Miguel

[#3]

Miguel,

Ah, thanks very much, I thought there was more involvement on the IT side.

Perfect response, thanks!

Simon

[rbecher]

Hi Simon,

from an IT point of view I would suggest:

1. Never grant access to a productive database for reporting/analysis. Always use a replication or backup copy or a data warehouse/data mart if you have.

2. Establish a view layer to decouple direct table access. So, you have the control. Also good if changes in data model could be encapsulated.

3. Create a special reporting/analytics database user. Grant SELECT right to the user for these views, only. Revoke any create/write rights from the database user.

4. Monitor all activities of this user. Adjust views, indexes or physical parameters to speed up queries related to the reporting/analytics requirements. I suggest also to make workshops with the developement frequently.

- Ralf

[Miguel_Angel_Baeyens]

Hi Simon,

You're welcome. Well, there's actually more involvement from the IT side, specially in regards to data governance and security (say for example index maintenance, backups, data integrity, who sees what, new fields and tables, new relations between them...). But as far as connections is concerned, you only need users in the server side and ODBC / Driver connections in the user/server side.

Regards.

Miguel

[#6]

Ralf,

Thanks for the input, very useful. 

As it stands it's just me trying to access a database for now, but when I come to roll out, I shall certainly take what you say into consideration.

Thanks

Simon

[IAMDV]

Simon,

If possible, please use application login in your connection string or hide/password protect the connection string script for security reasons. And please don't use SQL Server Admin Login (Assuming you are responsible to manage databases). If possible, please assign datareader role to the SQL Server login.

Good luck!

Cheers - DV