gsimardnet / PowerQueryNet

Run M Language (Power Query Formula Language) from anywhere.
MIT License
189 stars 32 forks source link

Azure SQL Connection #2

Closed activiz closed 6 years ago

activiz commented 6 years ago

Hi @gsimardnet, would it be possible to connect to an azure sql server instance or does the sql server have to be installed on the same machine?

I have attempted to update the credentials file.

Thanks in advance.

gsimardnet commented 6 years ago

For the moment, I only tested for on-premise SQL server using Windows integrated security. One convenient way to create credentials is to use Credentials.LoadFromFile and load file such as :

`

serverA\intanceX;databaseName01 serverB\intanceY;databaseName02

`

activiz commented 6 years ago

@gsimardnet, this is perfect, do you have an example of the credentials.xml using a username and password authentication or could you point me in the right direction?

gsimardnet commented 6 years ago

For the moment, it is not possible to provide a SQL userid and password. I will see if it's possible to add this option in an upcoming release. Thank you for your interest.

activiz commented 6 years ago

Hi @gsimardnet, I am attempting to run a test sql server on a server which I setup. I get an error when trying to connect. Could you please assist?

let Source = Sql.Database("dev-data-studio" , "TestClient" , [Query="SELECT TOP 10 * FROM dbo.Claim"]) in Source

`

dev-data-studio;TestClient Z:\TMP\eConnect\Archive\PM_Simplex_201702061100.xml Z:\Desktop\PowerQueryApp\MyExcel\List1.xls

`

image

activiz commented 6 years ago

didn't mean to close this issue

gsimardnet commented 6 years ago

Hi,

This error means that the SQL Server has returned a "Login failed". You must set the PowerQueryNet Service "Log On As" to a user that has access to your SQL Server.

Regards,

gsimardnet commented 5 years ago

I have added support for username and password in "CredentialSQL" in v1.1.0