CLOUD DATA PIPELINE PORTAL
Answers
-
0
-
Thank you @Helmut Heimann
1 -
hello @Helmut Heimann
I was able to successfully connect to Sql Database.
However in the schema tree, it seems im seeing only security schemas and not the schemas i should.
while on sql management studio i see
Im verifying internally also. However i would like to know if there is anything im missing on Board configuration.
Thank you
0 -
Hi @Khaleelah ,
well, I think your next step should be to get back to the cloud admin portal and add/configure the datasource in the data pipeline section.
Did you do that?
Best,
Helmut0 -
All these are done. Thats why i can connnect and see some schemas also. I did not specify and database so i should be having all the dbs, but im seeing only the security dbs.
This is from the admin portal
0 -
Hi @Khaleelah ,
aha, ok.
Then, what happens if you click on one of those schemas? You say they are all security dbs.
In what schema does your desired database sit—none of these?If you connect to the SQL server with Board's credentials, what do you see then in the Management Studio?
Best,
Helmut0 -
you mean to say i need to create the admin Portal Board User on the sqlserver. This is not done.
May be should create one and see.
Would it make a difference?
0 -
it might be that you will need to create that user. That user should have credentials to work on the tables you want to extract.
Best,
Helmut0 -
It looks like you are missing proper security within SQL Server. The username that you are using to connect to SQL Server, needs to be set with permissions to access the Database(Schema) where you want to access, and access to the Tables (or views) within the Database with a minimum of read access to the tables. To test if the username/password is working correctly (and eliminate Board as an issue), go into management studio and log in with that username/password confirm you can expand the database, tables (or views) and run a query as that user.
Bart Scott
CFO Solutions LLC0 -
Yes, this is what we are going to do. It is not a schema user
0 -
Yes it should be setup in the main SQL Security then granted access and a role with the correct privileges within the Database/Schema. Once it is fixed in SQL Server you should see the tables/views under the schema within Data Pipeline.
Bart Scott
CFO Solutions LLC0 -
Hi @Bart Scott
Actually in SSMS , i use the same user/password that i configured on BOARD Datasource and i can see the other DBs. But on Board i cannot.
What we are going to test is to add the Board portal admin as a user on the sqlserver and see how it works
0 -
hi @Bart Scott
Yes this is what we are going to do.
0 -
The only user the data pipeline uses is the username setup within the data pipeline connection. Database names are not shown within the data pipeline only the Schemas. Who owns the schemas in the database you are accessing? Many times it is dbo. Did you expand the Schema name used within the data pipeline to see if the tables/views are shown?
You can also do a quick test in a Query analyzer within Mgmt Studio. While logged in as the username/password, open a Query window with that user do a simple query like Select top 10 * from SCHEMA.TABLENAME and make sure you see data. Then copy and paste that exact query over to data pipeline test window and try to execute it.
Also ensure your database name is filled in on the first tab of the SQL Server Data Source in the data pipeline. You can also set the default schema within the advanced tab if you want to limit the schema Data Pipeline has access to.
Bart Scott
CFO Solutions LLC1 -
Hi @Bart Scott
Executed the query on both
On SSMS , i get table_catalogue as "ecl-mulesoft-staging-db-proj", on board i see only master and table_schema is only SYS
0 -
Do this step as a Sys Admin. Within SQL Server, go into the main security, logins, double click on the user you are using within the Data Pipeline. Go to the User Mapping tab, and check mark the database where the tables/views exist. Ensure that is has db_datareader, or a role that gives it the permissions to access the appropriate tables. Save that. Make sure the Default Schema is correct (in my example all the tables/views within that database are owned by dbo).
Log in as User. Open a query window while logged in as that user, change the database to the proper database and run a test query, so for example on my setup:
select top 10 * from dbo.GL_Trial_Balance;
Then go over the Data Pipeline, and try to run the same query against the table/view:
select top 10 * from dbo.GL_Trial_Balance;If that still doesn't work, try to add the db_ddladmin role, I do not remember if that is specifically needed or not from the Data Pipeline user.
Bart Scott
CFO Solutions LLC1
