Connecting to SQL Server DB using ODBC Datasource

[ArviLaanemets]

Hi!

A fresh member community here!

At start I have to apologize, as my own experience with Power BI is currently Nil! An user of Power BI asked for some help, and I did see, that he used Excel to read data from SQL server, and then read data from Excel with Power BI. I suggested to read data directly from SQL Server DB, and we failed with this!

I have created some number of databases on SQL Server (64 bit). For every database is defined a domain user group, members of which have access to this database (depending on DB, read only, or read and write.

Every user has datasource defined on his/her computer - depending on application used to read data 32 bit or 64 bit.  The specific datasource grants access to one SQL Server DB only (the database to connect to is defined). So long this system is working OK!

The user mentioned before had a 32 bit datasource (is used in Excel app) installed. When I tried to connect Power BI, he had to select between Excel, Access, DB-files or Visio. When selecting 'none' and entering datasource name ito field in next window, we could not continue.

We created another datasource  - 64  bit one. The result was same.

I have a collegue who uses Power BI. She created a similar datasource - and she could connect to it. But then she was asked for password to SQL Server, and as she is from IT staff too, she entered her domain PWD, and get connected. But all databases on SQL Server were displayed - not only DB the datasource was defined for (very logical - she had the access to those DB's)!

So my question is, how can user connect only to specific SQL Server DB from Power BI using ODBC Datasource - and to do this without Power BI asking additional PWD?

[v-juanli-msft]

Hi @ArviLaanemets 

You could connect to SQL Server directly with SQL SERVER connector in Power BI Desktop.

https://docs.microsoft.com/en-us/power-bi/service-gateway-sql-tutorial

If you'd like to connect to specific databases or tables,

you could write TSQL code when expanding the Advanced options.

Pass Parameters to SQL Queries

Or when connecting through an ODBC connection,

to provide a SQL statement that specifies what data to return, expand the Advanced Options area and then, in the SQL Statement (Optional)field, type or paste the SQL statement.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ArviLaanemets]

I have to check a couple of things out, based on your reply, but now I suspect this guy has to stick to previous scheme - he will read data into excel, and then into Power BI.

The problem is, that only IT staff can log into SQL server. All other people can only read info from databases, where is given the right to read data to any of domain groups this user belongs to. At least this seems logical in light of the member of IT staff being able to select a datasource. As she was allowed to log into SQL server, the datasource connecting to SQL server was shown for her. For user not member of IT no datasource was shown, as he hadn't rights to log into SQL Server.