SQL Server and SQL Server Integration Services – Informative Article
SQL Server and SQL Server Integration Services: Discussed and Explained
The concept of Relational Database Management System when brought forward by Microsoft was known as SQL Server. Architecture of SQL is a division where all the components combine and work, both; independently and together. This is done in order to process the services offered, in a defined way. This allows SQL Server to work smoothly.
The external SQL Server interface is developed by the Protocol Layer. All the operations conducted on the server are transmitted through a defined format known as the Tabular Data Stream (TDS). Basically, this is an Application Layer Protocol that helps to transfer data between the client and server (database).
Some other points that in a combination help make SQL an essential database management system for users include the following:
Data Storage
It is a collection of a variety of tables with all sorts of types including; primary types – decimal, integer, float, etc., varchar, and more.
Concurrency and locking:
The server permits users to make use of the SQL database concurrently by multiple types of clients. So it is required to take control of the simultaneous database access to the shared data. The two concurrency control modes provided are; pessimistic and optimistic.
SQL uses lock mechanism, in the pessimistic mode of concurrency control and they can be further classified as; shared & Exclusive Locks.
Data retrieval:
Data in SQL Server is retrieved via querying it and this query procedure is executed by the SQL Server variant; T-SQL. The order of steps in the procedure of querying for essentials to recall the data requested for is decided by the Query Processor.
Buffer Management
This part of SQL Server plays a critical role in reducing the Disc I/O while it buffers the pages into RAM. One can store up to 8 KB of pages in the buffered memory and this collection of all buffered pages is known as the Buffer Cache.
SQL Server and Its Versions
The entire database held by SQL Server is available as Primary (*.mdf) and Secondary (*.ndf) Database respectively. While an LDF file’s role is to hold the entire log details of the transactions carried out on any of the database.
Amongst all the versions of SQL Server; 2005, 2008 R2, 2014, and others; 2000 was the first version to be adding multiple performance measures to the Server. And out of all the measures introduced by SQL Server version 2000, SQL Server Integration Services or SSIS was the most vital one.
Detailing Of SQL Server and SQL Server Integration Services Security
The SSIS Security of SQL Server consist of a variety of layers offering a completely sound environ for the services. These layers constitute of the below mentioned components:
- Package Properties;
- Digital Signature;
- Operating System Permissions;
- Database roles.
When used in a combination for applying security measures, these components act as a defensive shield to the packages of SQL Server.
In order to interpret the concept of SQL Server and SQL Server Integration Services security, understanding the platform of SSIS is of primary importance. The forthcoming segment of this article discusses about the same as well as the attributes offered by it in SQL Server environ.
Understanding the SSIS Concept of Security
The SSIS or SQL Server Integration Services is a vital component that is associated to the Server. Normally used for carrying out a wide number and variety of operations related to data migration this platform has been structured considering the two mentioned elements:
-
Data Integration which happens to be an approach for combining the data from different set of resources. Thus, representing it in a manner that is unified and coordinated.
-
Workflow programs a set of applications that modify procedures to some point and even require manual agreement / customization or modification of activities sometimes.
The following functions are allowed to be performed at a higher level:
- Data retrieval through any source and loading of components into any source with a defining workflow.
- Carrying out a wide number procedures on the database including; calculation, conversion, etc.
This was an overview of the SSIS platform therefore, proceeding to the concept of SSIS security measures is feasible now.
The Concept of SSIS Security
Always using trusted mediums for launching the packages is one of the most important concepts of the SSIS Security measure. And prior to that, you must necessarily identity the source of package before opening it, which can be done by allotting certificates to packages.
The Perks of It: Unauthorized access to the server’s sensitive data can be kept under control via allotting identity features to a package. Also, it guarantees control on the SQL Server package configuration.
Even the logs, checkpoint files, and the configurations can be protected as well.
The Package Information displayed via SSMS (SQL Server Management Studio) Integration Services is offered integrity and protection by the medium of this platform.
A better understanding about the functional measures and features of the SSIS Security can be referred in the upcoming sections below.
-
Access On Package Data Components To Be Controlled: To limit or restrict access to package or its components, they are encrypted via “ProtectionLevel” property applied. Level of this type of security can be adjusted accordingly. Values get automatically encrypted for properties that are assigned to sensitive set of data by the IS (Integration Services). Only on providing the correct password, can an encrypted data be displayed.
-
Package Access To Be Controlled: SQL’s MSDB database or an XML file can be used for storing the IS Packages with file extension as .dtsx. In an MSDB database the “sysdtspackages” & “sysssispackages” for storing the package, tables are taken into usage. Thus, when database backup is created, associated packages automatically get backed up. In order to control access to these packages, three types of database roles are allotted to them:
- Db_ssisadmin
- Db_ssisltduser
- Db_ssisoperator
TIP: In case the file system is used for package storing, make sure that file or folders containing the packages are secure completely.
-
Access to Packages Containing Files To Be Control: Information encapsulated within the log, checkpoint files, and configuration packages require being prevented and protected due to the sensitive nature they have. Some of the factors associated with the approach are:
- Checkpoint file storage must only be done into the file system.
- Storage of Logs and Config files can be done using; SQL DB Tables or the File System.
- Extra provision of security is needed by the checkpoint files stored under file system.
-
IS Service Access To Be Controlled: “Windows Administrative” group members are assigned with package running access & stop roles. Meanwhile, users that are not member of the group are authorized to access or eliminate packages started only by them.
Conclusion: Security is a matter of concern regardless of the platform being discussed about. And understanding the detailed aspects of the SSIS Security measure helps prevent the SQL Server packages from unofficial access.
Disclosure: I am a real user, and this review is based on my own experience and opinions.
Thanks! And the answer is nope - Report Builder 2012 is out there and
works great! It's available at
www.microsoft.com
Plus, Report Builder 2014 is out. Here's a great page on what's new in it.
msdn.microsoft.com
Hope this helps!
David A. Klee