Introduction
This is the part 23 of the Data Mining articles. If you want to check the other chapters, they are all listed at the bottom of this article. Part 1 is an Introduction to Data Mining.
This time, we will explain about Data Mining security. We will give some tips, advices and some demos about the Data Mining roles and security permissions.
Getting Started
The security in SQL Server Data Mining is very complex and there are several recommendations to secure your SSAS Server. You first need to protect the Windows Server where the Data Mining model is installed. To protect the Windows Server is out of the scope of this article (it is a SQL Server site not a Windows Server site). For more information about protecting the OS. Review this link.
The next thing that you have to check is that the latest SSAS service pack is installed. In order to check, in the SQL Server Management Studio (SSMS) go to the SSAS properties.
Get the latest service pack
On the information page, check the Version. In this example the version 11.0.3000.0 is the SQL Server 2012 with SP1. There are build lists to find the latest Service Pack according to the version. You can see the build lists by version here.
Server Roles
SSAS uses Server roles and Database roles to administer Data Mining Projects. In order to add users or groups, you need to create Windows users/groups and then go the SSAS properties in SSMS. Click on the security page. You can add server administrators here with access to all the privileges on the instance.
This role allows you to create databases, set options, create backups and administer security roles.
General page
The General page contains important information to configure the security. In SSMS, select the properties of the SSAS Server and go to the General page. In order to see all the options, check the Show Advanced Properties checkbox.
There are very important options here like the DataDir, which shows the place where the physical data files are stored. You can check the path and change it here. If you consider it necessary, you can configure the security for this folder.
You also have the DataMining \ Algorithms sections. With these options, you can restrict the algorithms visible for all the users using the false value.
Changes to the DataMining Algorithms require you to restart SSAS.
You can restart SSAS using the SQL Server Configuration Manager.
In this example, we disabled the SQL Server Association Rules in the general property using the false value. As you can see, when you try to select a data mining technique, the Asociation Rules are not visible.
Another option is the AllowedBrowsing, which lets you select the visible folders for backups and restorations. This option is very important because it contains private information . Make sure that the folders used are secure. In this sample, we will add the "c:\sql folder" as a new folder to browse.
In order to verify that the value was added, backup your SSAS database with the data mining model.
Press the Browse button to select a path to save the backup.
As you can see, the c:\sql folder was added in the list of folders where you can save the Data Mining backups.
Encrypt your backup files
Your backup file can be a security hole in the security of the Data Mining Model. If you want a secure database, encrypt your backups. Otherwise, a malicious person could stole your information with the backup file.
Database Mining Roles
You can create Data Mining users. To do this create Microsoft Groups first and then assign Windows Users to the groups.
By default, there are three main SSAS roles. The Administrator role, the Process role and the Read role. To create them create the Windows roles in Active Directory users and groups or other tool of your preference.
To create the SSAS Database Roles, use the New role option in SSMS.
You could also use the SSDT (SQL Server Data Tools) to create roles.
You could assign the default roles or check the permissions individually. The Administrators has full control over the databases, the Process database user can process different components and the Read role allows you to read the data only.
In Membership, you can assign Groups of windows users or Individual users. We recommend using Groups, specially if the number of users is high.
By default, you need to Press the object types button to change the object types.
Check the Groups object to add Windows groups.
Press the advanced option to search the Windows Groups.
With the find now button you can search the windows and user groups available.
Select the Windows Groups appropriate for your environment.
You can configure permissions for the Datasource.
In the Mining Structure tab, you can select the structure and see all the models related to the structure. You can restrict the access to the structure or individual models. You can also restrict the Drill through permission also at the Mining Structure and Mining Models levels.
The Ports
The port used by default for SSAS is port 2239. In case it is not, you can use the netstat command to view ports in use:
netstat /abo >>c:\output.txt
The command creates a file named output.txt in the c drive.
If you open the file, you can search the msmdsrv.exe file. The port used in this sample is the 56686.
Make sure to use firewalls and enable only the necessary ports in your servers to protect your machine.
Conclusion
As you can see, the security is a wide topic. In the next chapter, you will be able to see more tips about Data Mining Security.
References
http://technet.microsoft.com/en-us/library/bb500208(v=sql.105).aspx
