Hello, im trying to config polybase to take data from my other sql and struggling a bit here. Does anyone finished such task successfully? The thing is to use windows authentication here.
So i already have database master key. With that im going to create database scoped credentials:
CREATE CREDENTIAL [AppCredWind] WITH IDENTITY = N'domain\user', SECRET = N'password'
Next step is creating external data source:
CREATE EXTERNAL DATA SOURCE [sourcename] WITH (LOCATION = N'sqlserver://servername',CONNECTION_OPTIONS='Trusted_Connection=yes', CREDENTIAL = [AppCredWind]);
NOTE: i tried trough wizard in Azure studio but it failed miserably throwing that servername to which im trying to connect accepts only windows authentication. Due to this i create data source manually adding 'Trusted_Connection=yes' which according to documentation is ok.
Data source created then im off to create table and here is a problem:
CREATE EXTERNAL TABLE externalBackups(
[databasename] [varchar](max) NOT NULL,
Now here is a problem - due to trusted_connection it tries to connect using actually service account and not account provided in credentials. As a result it throws that login cannot be matched.
If i remove trusted_connection it tries to login as SQL account and gets error that server is configured to accept only windows auth.
Any ideas how to tackle that problem?