April 13, 2019 at 10:32 am
HI EVeryone
i wanted to know if I have multiple data sources and wanted to use SSIS to extract data from them but I want to setup security so only specific service accounts can access certain data sources and access certain tables / rows etc. is this done via connection managers and packages?
Will this security be sorted in the process if I’m moving the data to a data warehouse where the data will get analyzed using SSAS?
Have a good weekend
kal
April 13, 2019 at 10:52 am
That totally depends on how you set up the data sources. If they are using Windows Authentication, then they will be using the authentication of the account running the package; if that's the Agent, it'll be the agent's credentials, if it's a User executing the package in SSISDB it'll be the user's. Without any information to go on, that's the best I can answer.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 13, 2019 at 1:39 pm
Hi Thom
we will be getting data from SQL server, excel files, csv files, oracle database and unstructured data from a PII database. So how do I make sure that the data sources are accessed only by those who are authenticated / authorized?
Kal
April 13, 2019 at 7:43 pm
Not sure what you mean by only those authenticated. Only those who have permission to execute packages in SSISDB will be able to execute the packages; but you can't decode a deployed package in SQL Server. Deployed packages in SSISDB are encrypted and Microsoft don't publish the encryption key(s).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 13, 2019 at 7:50 pm
Hi Thom
so what is the best practice here?
Windows authentication and running the package with a service account or a user executing the package in SSISDB? I’m happy to say I have NO experience with SSIS at all.
Keep in mind this data which is going to be ETL will be loaded into a DW then accessed via SSAS to generate cubes which will then be used in SSRS.
So I need the best practice for authentication / authorization for all steps.
How will the source data be kept “secured” ??
i hope you understand my question
kal
April 13, 2019 at 8:37 pm
The source data is totally separate to SSIS. How you keep an Excel file secure has nothing to do with SQL Server. My suggestion would be store it in a share that only the people who should have access to it, have access to that share.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 14, 2019 at 8:22 am
Sorry Thom
i didn’t meant the source data I meant the data source which SSIS will use to connect to the data.
How do we secure the data source connection?
Correct me if I’m mistaking with my logic
1. Connect to data source using SSIS
2. Extract data using packages which can be encrypted right?
3. Transform the data using SSIS
4. Load the cleansed data into a data warehouse using SSIS.
5. Analyze the data in the data warehouse using SSAS and generate a tabular model.
6. Connect to SSAS using power BI and generate reports in Power BI reporting server
is this the correct logic?
Kal
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply