Forum Discussion
SQL Server Express set up
SQL Servers wont let just anyone query data. You will need a username/password that the instance will be happy with. These will be the credentials you will use for your data gateway.
The only reason you might need SSMS is so you can log onto the instance as an administrator and create an account for you.
If you already know a username/password for your instance (eg, sa and <password>) then you can use those and there is no need for SSMS (allthough ideally you shouldn't be using sa).
Even if you do download SSMS you'll need to know the password for SA before you can configure your instance.
I take it the DB is installed on a different machine?
Just connect to the db, but I recommend that you create parameters for the server and db so that it will be easy to change in the future. When you connect, you will have to choose your authentication method, and if a different user opens the pbix file on their machine, it will ask for their credentials. As for PBI Service, once you publish, the service identifies the user based on their Power BI log in, so you will have to set up RLS in PBI or at the source to create the desired views for your users.
well I'm glad I asked - thanks for your input; unclear to me - -
1. "create parameters for the server and db"
2. "When you connect, you will have to choose your authentication method"
- - this is specifically what I was searchng for in documentation - what are the choices? and what defines what is to be used?
3. "and if a different user opens the pbix file on their machine, it will ask for their credentials"
- - am assuming you mean the regular credentials that an authorized user uses for the business application itself
no SQL DB admin people - - SQL Server Express is embedded inside of the commercial application - so not sure to what degree the current users have a handle on this info.
Your instance of SQL Server will have at the very least, an Administrator login. Often called 'sa' and you will need to know the password.
Once you know this, you can connect and administer the instance using SQL Server Management Studio.
You can create new logins/passwords, assign permissions (eg, PBI account to be ReadOnly) etc.
Then you can use these credentials for Power BI.
SQL Express often has a named instance along the lines of <machinename>\SQLExpress or similar. SSMS should help you find this.
hmmm the authentication info I find as part of Gateway set up .... but nothing at all on this for your basic Get Data in PBI desktop....
surprised to learn one must install SSMS and use that....
