Forum Discussion
SQL Server Express set up
On premise. Just a Get Data scenario for now. A commercial business application is platformed on SQL Server Express 2012.
I believe therefore there are .mdf files with the tables. For initial set up and modeling is it better to copy those .mdf files and set up the PBI Get Data to the copy files? I don't see a strong reason to do this other than the initial Get could potentially bog down the production application until it completes. Or is that too cautious?
In the long run a permanent link with refresh via a gateway is planned so if the intial set up is off of copied files - that would need to change.
At Documentation area I don't find any info regarding credentials. Server Name and Database (optional) is presented in the Get Data process - - but are there further credentials needed along the way? - just want all the ducks lined up going into this.
Would welcome input if you've done this Get Data scenario....
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?
8 Replies
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.
Hi CahabaData
Are you thinking of trying to open and read the MDF files directly? Or query a running instance of SQL Server Express?
Because it is the Express edition - I don't think the file size / record count is overly large - - and also in my searching I believe I saw that Power Query is not available to 2012 Express. So my thinking was to pull in all the data.
