October 30, 2019 at 10:35 am
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,
)
WITH (
LOCATION='database.dbo.table',
DATA_SOURCE=[sourcename]
);
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?
October 30, 2019 at 2:51 pm
You're connecting Sql Server to Sql Server? Why not use a linked server?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
October 30, 2019 at 6:33 pm
in the few experiments I have done with Polybase, it seems that it has a lot of JAVA underneath the covers. The External Data Sources are just JDBC connection strings, really. While I have not connected without the service account, you may try to use some of this:
It looks like you may have to do a lot of setup work before this will work, but it is worth a shot.
October 31, 2019 at 7:47 am
@scdecade - because i want to test Polybase, just that. Also it has option to connect to SQL so why not use it this way? One would think SQL Server to SQL Server would be easiest way.
@crow1969 - thanks i will take a look. However according to https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-ver15 it is using ODBC not JDBC - "Refer to the ODBC product documentation for a list of permitted CONNECTION_OPTIONS"
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply