Saturday, August 27, 2016

The "Public" Database Role in SQL Server

In SQL Server, the Public database role in SQL server is a special database role that is given by default to all logins. You cannot remove a login's membership to this role because this behavior is built into SQL Server.










































But what this role does? Should we make use of it or at the end of the day just ignore it?

When you create a new login (and consequently a new database user) in SQL Server and do not give it any permissions anywhere (that is securable objects), then this database user inherits the permissions the public database role has.

So, let's see an example. On a SQL Server 2014 instance I have just created a login named 'testLogin' (not quite  unique, isn't it? :) and just granted access to the 'Adventureworks2014' database without doing anything else. As you can see from the below screenshot, my login was just granted the Public database role on the database.











































The question now is what permissions the 'testLogin' inherited on AdventureWorks2014? I have not provided any permission so it's just the permissions provided by the Public database role.

If we check the securableS for the Pulbic database role in AdventureWorks2014 security, we can see that the Public database role is granted with SELECT access to certain system catalog views such as:

  • sys.all_columns
  • sys.all_objects
  • sys.all_parameters
  • sys.all_sql_modules
  • sys.all_views
  • sys.allocation_units
  • sys.assemblies
  • etc.
















































Now here comes the security recommendation:

Even though the Public database role is granted by default SELECT permissions to certain system catalog views, you should never, ever add more permissions to this role. Just leave it as it is. If you make the mistake and add more permissions to the public database role then this will mean that any login that will be granted access to the database it will inherit all these permissions. So please, don't do that!

Let's try again to rephrase this with even more plain words: any permissions the Public database role has, are automatically granted to all database users. That's why you should never add permissions to this role.

By the way, if you want to check if you have accidentally added more permissions to this role, you can try the below query:

USE [Database Name];
GO

SELECT 
DB_NAME() as DBName,
pm.[permission_name] as PermissionName,
ob.[name] as ObjectName,
pm.class_desc as ObjectType,
ob.type_desc as TypeDescription
FROM sys.database_permissions pm
INNER JOIN sys.database_principals pr ON pm.grantee_principal_id= pr.principal_id
LEFT JOIN sys.objects ob ON pm.[major_id] = ob.[object_id]
WHERE [state]='G' and major_id>=0 and pr.[name]='public' and ob.[name] IS NOT NULL;
GO

If the above query return any results, you should revise the permissions granted to the Public database role for the specific database.

Read more on this article...

Monday, August 15, 2016

Artemiou Data Tools: Free Online SQL Services

Three years ago, back in 2013, I founded the "Artemiou Data Tools" community initiative.

Under the "Artemiou Data Tools" initiative I aim at developing simple yet powerful data tools for the Microsoft Windows Operating System. The majority of tools in the "Artemiou Data Tools" software collection mainly interact with SQL Server, thus providing a richer user experience and enabling the user to get the most out of the world's more powerful data platform.

Since 2013 I have developed 6 free tools for SQL Server and 2 enterprise tools which however have also Community Editions which are free.

Free Tools

Enterprise Tools
You, my fellow SQL Server community members embraced my efforts and supported my work by downloading my software tools along with providing valuable feedback.
That's why, now, I'm further expanding this initiative by also including free online SQL Server services! These services are all free and provide useful information about different SQL Server topics on a demand-basis. 

Currently there are two online SQL services available:


SQL Server Supported Operating Systems, takes as user input a version of SQL Server and returns the supported Operating Systems for the specific SQL Server version.
























SQL Server Latest Service Pack Info, takes as user input a version of SQL Server and provides info about its latest service pack along with download links and more.























More free online SQL Server services will be keep on constantly added, thus making Artemiou Data Tools not only your main SQL Server tools hub, but also your hub of online SQL Server services!

Thank you for your support! 

Feel free to share the news!

Read more on this article...

Monday, July 4, 2016

Why You Need to Secure Your SQL Server Instances

Database Management Systems (DBMSs) store data. They sure have added functionality and a huge set of significant features but again, in the end of the day, they store data. Your data. This makes your DBMSs one of the most valuable assets in your Organization and that's why you need to keep them as secure as possible during their entire life cycle  within your Organization.

SQL Server is a very powerful data platform and part of this power, is to allow the user to control different settings, thus making it work the best for his/her needs. However, as in all systems, if these settings are misconfigured, or the proper precautions are not taken on the user’s side, then along with the functionality the user wants to enable, he or she possibly will create security risks.

Take for example, the ‘Password Expiration’ option. In case you have a SQL login which is used as a service account, then this could be a reason for not to have the ‘Password Expiration’ enabled for that login. If however, you have an SQL login which is used by a physical person and the ‘Password Expiration’ option is not enabled, this increases the risk of having the password guessed more easily than in the case where the password expires every X days (i.e. every 90 days) and thus the user needs to enter a new one.

Figure 1: Start Page.
Other examples of security risks are: using the same password as the username for SQL Logins, having 'xp_cmdshell' enabled without really needing it and without the proper design and accesses, having the BUILTIN\Administrators local windows group on the database server as SysAdmins, etc.

There are many settings which can be incorrectly set by the user, either because of naivety, or lack of deep understanding about these settings, or any other reason.

By the time a DBMS hosts a single database, it is critical that you keep that DBMS instance as secure as possible. Misconfiguring your instance can be a source of vulnerabilities so you need to periodically check you instance about related security risks and take remediation actions when and where needed.

It is based on all the above that I have been developing during the last two years "DBA Security Advisor". DBA Security Advisor is a powerful tool i released a few days ago, which assesses SQL Server instances for potential security risks based on a proven best practices set of security checks. Furthermore it provides recommendations for the detected security risks as well as remediation scripts and methods.

DBA Security Advisor comes in two editions: (i) A Community Edition which is free but with a limited set of security checks and limited functionality, and (ii) An Enterprise Edition where all security checks and other features are available. You can compare the available features per edition on this link.

The workflow of DBA Security Advisor (Enterprise Edition) is very straightforward:

1. You connect to a single or multiple SQL Server instances.
2. You select the security checks to run against the connected SQL Server instance(s) and run the assessment.
3. You go through the generated report with the security findings.
4. You study the recommendations and remediation scripts/methods and act accordingly towards resolving the security risks.
5. You re-run the security assessment and check if the previously-reported security risks have been eliminated after you took actions.

Figure 2: Connect to Multiple Instances (Enterprise Edition).




























Figure 3: Select Security Checks (Enterprise Edition).




























Figure 4: Embedded Report Viewer: Sample Security Risks.




























Figure 5 Standalone Security Report Viewer with Recommendations and Remediation Scripts and Methods.


























I believe that you will find DBA Security Advisor extremely useful. It will help you secure your SQL Server instances, as well as become compliant with a large number of security best practice factors. Test the Community Edition today which is free, and after you are convinced that DBA Security Advisor can help you in your everyday SQL Server administration and hardening process, you can consider upgrading to the Enterprise Edition, and thus unlock all security checks and the rest of its powerful features.



Resources:



Cheers,

Artemakis Artemiou
Microsoft Data Platform MVP
https://www.aartemiou.com
https://www.dbasecadvisor.com
https://www.inmemoltpsim.com
Read more on this article...

Tuesday, June 28, 2016

DBA Security Advisor is Now Out!

Today is big day. Today, DBA Security Advisor is finally released!

It has been a long journey. Two years of research, development, testing, QA, and the list goes on!

I work with SQL Server for more than 11 years and I have seen hundreds of SQL Server installations and setups. I have seen best practices and bad practices. I have seen deep knowledge in administering SQL Server, but I have also seen naivety in administering such a powerful RDBMS and believe me, naivety in database administration is not something good.

SQL Server is a very powerful data platform and part of this power, is to allow the user to control different settings, thus making it work the best for his/her needs. However, similarly as in all systems, if these settings are misconfigured, or the proper precautions are not taken on the user’s side, then this could create possible security risks.

Take for example, the ‘Password Expiration’ option. In case you have a SQL login which is used as a service account, then this could be a reason for not to have the ‘Password Expiration’ enabled for that login. If however, you have an SQL login which is used by a physical person and the ‘Password Expiration’ option is not enabled, this increases the risk of having the password guessed more easily than in the case where the password expires every X days (i.e. every 90 days).

Another example, is using the same password as the username for SQL Logins. This is a critical security risk and you should never do this.

DBA Security Advisor, via a rich set of security checks, assesses your SQL Server instances against potential security risks and recommends solutions along with remediation scripts and methods.

Download the Community Edition today! Feel free to try DBA Security Advisor! After you are convinced that it can really help you, then you can consider upgrading to the Enterprise Edition where you can have access to all the security checks as well as to all the great features of DBA Security Advisor.





Resources:



Cheers,
Artemakis
Read more on this article...

Saturday, June 25, 2016

Announcing DBA Security Advisor - A Security Tool for SQL Server

After almost two years of heavy development and extensive testing, in three days, on June 28 2016, I will be releasing my brand new software tool for SQL Server.

The name of the tool is 'DBA Security Advisor' and as the name implies, it is a database security tool. DBA Security Advisor assesses SQL Server instances against a rich set of security checks and provides recommendations along with remediation scripts and methods, all based on proven security best practices for SQL Server.

Figure 1: Connecting to Multiple SQL Server Instances.




























DBA Security Advisor comes in two editions: (i) The Community Edition which is free but with
limited security checks and functionality, and (ii) The Enterprise Edition with requires a license and where all security checks and features are available. You can check the features of each edition here.

DBA Security Advisor provides a variety of security checks in order to enable the DBA to perform a rich assessment of the SQL Server instance or instances and get meaningful insights about their configuration from the security perspective.

Figure 2: Available Security Checks in Enterprise Edition
(Community Edition, among other, supports fewer security checks).





























After the assessment is completed, DBA Security Advisor generates a comprehensive report with the results of the assessment and other important information. Also, in the Enterprise Edition of the tool, you can get recommendations and remediation scripts and methods.

Figure 3: Embedded Report Viewer.





























Figure 4: Standalone Security Report Viewer (Enterprise Edition).

























Note: DBA Security Advisor supports SQL Server 2005 or later. 


For more information, you can download the DBA Security Advisor's datasheet below:










Also you can visit DBA Security Advisor's official website.

Feel free to register to DBA Security Advisor's newsletter and stay up to date with the latest news about this remarkable software product.

Read more on this article...

Saturday, May 28, 2016

Operating System Requirements for all SQL Server Versions and Editions


SQL Server is the world's most powerful data platform and it is constantly evolving. From release to release significant new features are added. Besides the features and functionality, one of the typical question DBAs have is which are the Operating System requirements for each version and edition of SQL Server. In this post you can find this information for all versions and editions gathered from different MSDN Library articles. In the end of each table there is a reference to the source document on MSDN in case you need to find more information (i.e. 32-bit vs. 64 bit, etc.).


SQL Server 2016 - Operating System Requirements
SQL Server 2016 Enterprise Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
SQL Server 2016 Standard Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows 10 Home
Windows 10 Professional
Windows 10 Enterprise
Windows 8.1
Windows 8.1 Pro
Windows 8.1 Enterprise
Windows 8
Windows 8 Pro
Windows 8 Enterprise
SQL Server 2016 Web Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
SQL Server 2016 Developer Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows 10 Home
Windows 10 Professional
Windows 10 Enterprise
Windows 8.1
Windows 8.1 Pro
Windows 8.1 Enterprise
Windows 8
Windows 8 Pro
Windows 8 Enterprise

SQL Server 2016 Express

Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows 10 Home
Windows 10 Professional
Windows 10 Enterprise
 Windows 8.1
 Windows 8.1 Pro
 Windows 8.1 Enterprise
 Windows 8
 Windows 8 Pro
 Windows 8 Enterprise

 

For more info please visit: https://msdn.microsoft.com/en-us/library/ms143506.aspx

 




SQL Server 2014 - Operating System Requirements
SQL Server 2014 Enteprise Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essential
Windows Server 2012 R2 Foundation
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows Server 2008 R2 SP1 Datacenter
Windows Server 2008 R2 SP1 Enterprise
Windows Server 2008 R2 SP1 Standard
Windows Server 2008 R2 SP1 Web
Windows Server 2008 SP2 Datacenter
Windows Server 2008 SP2 Enterprise
Windows Server 2008 SP2 Standard
Windows Server 2008 SP2 Web

SQL Server 2014 Business Intelligence

Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows Server 2008 R2 SP1 Datacenter
Windows Server 2008 R2 SP1 Enterprise
Windows Server 2008 R2 SP1 Standard
Windows Server 2008 R2 SP1 Web
Windows Server 2008 SP2 Datacenter
Windows Server 2008 SP2 Enterprise
Windows Server 2008 SP2 Standard
Windows Server 2008 SP2 Web

SQL Server 2014 Standard

Windows 10 Home
Windows 10 Professional
Windows 10 Enterprise
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows Server 2008 R2 SP1 Datacenter
Windows Server 2008 R2 SP1 Enterprise
Windows Server 2008 R2 SP1 Standard
Windows Server 2008 R2 SP1 Foundation
Windows Server 2008 R2 SP1 Web
Windows 8.1
Windows 8.1 Pro
Windows 8.1 Enterprise
Windows 8
Windows 8 Pro
Windows 8 Enterprise
Windows 7 SP1 Ultimate
Windows 7 SP1 Enterprise
Windows 7 SP1 Professional
Windows Server 2008 SP2 Datacenter
Windows Server 2008 SP2 Enterprise
Windows Server 2008 SP2 Standard
Windows Server 2008 SP2 Foundation
Windows Server 2008 SP2 Web

SQL Server 2014 Web

Windows 10 Home
Windows 10 Professional
Windows 10 Enterprise
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows Server 2008 R2 SP1 Datacenter
Windows Server 2008 R2 SP1 Enterprise
Windows Server 2008 R2 SP1 Standard
Windows Server 2008 R2 SP1 Web
Windows Server 2008 SP2 Datacenter
Windows Server 2008 SP2 Enterprise
Windows Server 2008 SP2 Standard
Windows Server 2008 SP2 Web

SQL Server 2014 Developer

Windows 10 Home
Windows 10 Professional
Windows 10 Enterprise
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows Server 2008 R2 SP1 Datacenter
Windows Server 2008 R2 SP1 Enterprise
Windows Server 2008 R2 SP1 Standard
Windows Server 2008 R2 SP1 Web
Windows 8.1
Windows 8.1 Pro
Windows 8.1 Enterprise
Windows 8
Windows 8 Pro
Windows 8 Enterprise
Windows 7 SP1 Ultimate
Windows 7 SP1 Enterprise
Windows 7 SP1 Professional
Windows 7 SP1 Home Premium
Windows 7 SP1 Home Basic
Windows Server 2008 SP2 Datacenter
Windows Server 2008 SP2 Enterprise
Windows Server 2008 SP2 Standard
Windows Server 2008 SP2 Web

SQL Server 2014 Express

Windows 10 Home
Windows 10 Professional
Windows 10 Enterprise
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows Server 2008 R2 SP1 Datacenter
Windows Server 2008 R2 SP1 Enterprise
Windows Server 2008 R2 SP1 Standard
Windows Server 2008 R2 SP1 Foundation
Windows Server 2008 R2 SP1 Web
Windows 8.1
Windows 8.1 Pro
Windows 8.1 Enterprise
Windows 8
Windows 8 Pro
Windows 8 Enterprise
Windows 7 SP1 Ultimate
Windows 7 SP1 Enterprise
Windows 7 SP1 Professional
Windows 7 SP1 Home Premium
Windows 7 SP1 Home Basic
Windows Server 2008 SP2 Datacenter
Windows Server 2008 SP2 Enterprise
Windows Server 2008 SP2 Standard
Windows Server 2008 SP2 Foundation
Windows Server 2008 SP2 Web




SQL Server 2008 R2 - Operating System Requirements

SQL Server 2008 R2 Datacenter

Windows Server 2003 SP2 Datacenter
Windows Server 2003 SP2 Enterprise
Windows Server 2003 SP2 Standard
Windows Server 2003 R2 SP2 Datacenter
Windows Server 2003 R2 SP2 Enterprise
Windows Server 2003 R2 SP2 Standard
Windows Server 2008 SP2 Datacenter
Windows Server 2008 SP2 Datacenter without Hyper-V
Windows Server 2008 SP2 Enterprise
Windows Server 2008 SP2 Enterprise without Hyper-V
Windows Server 2008 SP2 Standard
Windows Server 2008 SP2 Standard without Hyper-V
Windows Server 2008 SP2 Web
Windows Server 2008 for Windows Essential Server Solutions
Windows 2008 R2 Datacenter
Windows 2008 R2 Enterprise
Windows 2008 R2 Standard
Windows 2008 R2 Web
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation

SQL Server 2008 R2 Enterprise

Windows Server 2003 SP2 Datacenter
Windows Server 2003  SP2 Enterprise
Windows Server 2003 SP2 Standard
Windows Server 2003 R2 SP2 Datacenter
Windows Server 2003 R2 SP2 Enterprise
Windows Server 2003 R2 SP2 Standard
Windows Server 2008 SP2 Datacenter
Windows Server 2008 SP2 Datacenter without Hyper-V
Windows Server 2008 SP2 Enterprise
Windows Server 2008 SP2 Enterprise without Hyper-V
Windows Server 2008 SP2 Standard
Windows Server 2008 SP2 Standard without Hyper-V
Windows Server 2008 SP2 Web
Windows 2008 R2 Datacenter
Windows 2008 R2 Enterprise
Windows 2008 R2 Standard
Windows 2008 R2 Web
Windows Server 2008 R2 for Windows Essential Server Solutions
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation

SQL Server 2008 R2 Standard

 

Windows XP Professional SP2
Windows Server 2003 SP2 Datacenter
Windows Server 2003 SP2 Enterprise
Windows Server 2003 SP2 Standard
Windows Server 2003 R2 SP2 Datacenter
Windows Server 2003 R2 SP2 Enterprise
Windows Server 2003 R2 SP2 Standard
Windows Vista SP2 Ultimate
Windows Vista SP2 Enterprise
Windows Vista SP2 Business
Windows Server 2008 SP2 Datacenter
Windows Server 2008 SP2 Datacenter without Hyper-V
Windows Server 2008 SP2 Enterprise
Windows Server 2008 SP2 Enterprise without Hyper-V
Windows Server 2008 SP2 Standard
Windows Server 2008 SP2 Standard without Hyper-V
Windows Server 2008 SP2 Web
Windows Server 2008 SP2 for Windows Essential Server Solutions
Windows Server 2008 SP2 Foundation Server
Windows 7 Ultimate
Windows 7 Enterprise
Windows 7 Professional
Windows Server 2008 R2 Datacenter
Windows Server 2008 R2 Enterprise
Windows Server 2008 R2 Standard
Windows Server 2008 R2 Web
Windows Server 2008 R2 for Windows Essential Server Solutions
Windows Server 2008 R2 Foundation Server
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows 8
Windows 8 Professional
Windows 8.1
Windows 8.1 Professional

SQL Server 2008 R2 Developer

 

Windows XP SP2 Professional
Windows Server 2003 SP2 Datacenter
Windows Server 2003 SP2 Enterprise
Windows Server 2003 SP2 Standard
Windows Server 2003 R2 SP2 Datacenter
Windows Server 2003 R2 SP2 Enterprise
Windows Server 2003 R2 SP2 Standard
Windows Vista Ultimate
Windows Vista Home Premium
Windows Vista Home Basic
Windows Vista Enterprise
Windows Vista Business
Windows Server 2008 SP2 Datacenter
Windows Server 2008 Datacenter without Hyper-V
Windows Server 2008 SP2 Enterprise
Windows Server 2008 SP2 Enterprise without Hyper-V
Windows Server 2008 SP2 Standard
Windows Server 2008 Standard without Hyper-V
Windows Server 2008 SP2 Web
Windows 7 Ultimate
Windows 7 Home Premium
Windows 7 Home Basic
Windows 7 Enterprise
Windows 7 Professional
Windows Server 2008 R2 DataCenter
Windows Server 2008 R2 Enterprise
Windows Server 2008 R2 Standard
Windows Server 2008 R2 Web
Windows Server 2008 R2 for Windows Essential Server Solutions
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows 8
Windows 8 Professional
Windows 8.1
Windows 8.1 Professional

SQL Server 2008 R2 Workgroup

 

Windows XP SP2 Professional
Windows Server 2003 SP2 Datacenter
Windows Server 2003 SP2 Enterprise
Windows Server 2003 SP2 Standard
Windows Server 2003 R2 SP2 Datacenter
Windows Server 2003 R2 SP2 Enterprise
Windows Server 2003 R2 SP2 Standard
Windows Vista SP2 Ultimate
Windows Vista SP2 Enterprise
Windows Vista SP2 Business
Windows Server 2008 SP2 Datacenter
Windows Server 2008 SP2 Datacenter without Hyper-V
Windows Server 2008 SP2 Enterprise
Windows Server 2008 SP2 Enterprise without Hyper-V
Windows Server 2008 SP2 Standard
Windows Server 2008 SP2 Standard without Hyper-V
Windows Server 2008 SP2 Web
Windows Server 2008 R2 for Windows Essential Server Solutions
Windows Server 2008 SP2 Foundation Server
Windows 7 Ultimate
Windows 7 Enterprise
Windows 7 Professional
Windows Server 2008 R2 Datacenter
Windows Server 2008 R2 Enterprise
Windows Server 2008 R2 Standard
Windows Server 2008 R2 Web
Windows Server 2008 R2 Foundation Server
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows 8
Windows 8 Professional
Windows 8.1
Windows 8.1 Professional

SQL Server 2008 R2 Web

 

Windows Server 2003 SP2 Datacenter
Windows Server 2003 SP2 Enterprise
Windows Server 2003 SP2 Standard
Windows Server 2003 R2 SP2 Datacenter
Windows Server 2003 R2 SP2 Enterprise
Windows Server 2003 R2 SP2 Standard
Windows Server 2008 SP2 Datacenter
Windows Server 2008 SP2 Datacenter without Hyper-V
Windows Server 2008 SP2 Enterprise
Windows Server 2008 SP2 Enterprise without Hyper-V
Windows Server 2008 SP2 Standard
Windows Server 2008 SP2 Standard without Hyper-V
Windows Server 2008 SP2 Web
Windows Server 2008 R2 Enterprise
Windows Server 2008 R2 Standard
Windows Server 2008 R2 Web
Windows Server 2008 R2 Datacenter
Windows Server 2008 R2 for Windows Essential Server Solutions
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation

SQL Server 2008 R2 Express 

 

Windows Server 2003 SP2 Datacenter
Windows Server 2003 SP2 Enterprise
Windows Server 2003 SP2 Standard
Windows Server 2003 R2 SP2 Datacenter
Windows Server 2003 R2 SP2 Enterprise
Windows Server 2003 R2 SP2 Standard
Windows Vista SP2 Ultimate
Windows Vista SP2 Home Premium
Windows Vista SP2 Home Basic
Windows Vista SP2 Enterprise
Windows Vista SP2 Business
Windows Server 2008 SP2 Datacenter
Windows Server 2008 SP2 Datacenter without Hyper-V
Windows Server 2008 SP2 Enterprise
Windows Server 2008 SP2 Enterprise without Hyper-V
Windows Server 2008 SP2 Standard
Windows Server 2008 SP2 Standard without Hyper-V
Windows Server 2008 SP2 Web
Windows Server 2008 SP2 Foundation Server
Windows 7 Ultimate
Windows 7 Home Premium
Windows 7 Home Basic
Windows 7 Enterprise
Windows 7 Professional
Windows Server 2008 R2 Datacenter
Windows Server 2008 R2 Enterprise
Windows Server 2008 R2 Standard
Windows Server 2008 R2 Web
Windows Server 2008 R2 for Windows Essential Server Solutions
Windows Server 2008 R2 Foundation Server
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows 8
Windows 8 Professional
Windows 8.1
Windows 8.1 Professional




SQL Server 2008 - Operating System Requirements
SQL Server 2008 Enterprise

Windows Server 2003 SP2 Standard
Windows Server 2003 SP2 Datacenter
Windows Server 2003 SP2 Enterprise
Windows Server 2008 Standard
Windows Server 2008 Standard without Hyper-V
Windows Server 2008 Datacenter
Windows Server 2008 Datacenter without Hyper-V
Windows Server 2008 Enterprise
Windows Server 2008 Enterprise without Hyper-V
Windows Server 2008 R2 Web
Windows Server 2008 R2 Standard
Windows Server 2008 R2 Enterprise
Windows Server 2008 R2 Datacenter
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation

SQL Server 2008 Standard 

Windows XP Professional
Windows Server 2003 SP2 Standard
Windows Server 2003 SP2 Datacenter
Windows Server 2003 SP2 Enterprise
Windows Vista Ultimate
Windows Vista Enterprise
Windows Vista Business
Windows Server 2008 Web
Windows Server 2008 Standard
Windows Server 2008 Standard without Hyper-V
Windows Server 2008 Datacenter
Windows Server 2008 Datacenter without Hyper-V
Windows Server 2008 Enterprise
Windows Server 2008 Enterprise without Hyper-V
Windows Small Business Server 2008
Windows Server 2008 for Windows Essential Server Solutions
Windows Server 2008 without Hyper-V for Windows Essential Server Solutions
Windows 7 Ultimate
Windows 7 Enterprise
Windows 7 Professional
Windows 2008 R2 Web
Windows 2008 R2 Foundation Server
Windows 2008 R2 Standard
Windows 2008 R2 Enterprise
Windows 2008 R2 Datacenter
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows 8
Windows 8 Professional
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows 8.1
Windows 8.1 Professional

SQL Server 2008 Developer 

Windows XP Professional
Windows Server 2003 SP2 Standard
Windows Server 2003 SP2 Datacenter
Windows Server 2003 SP2 Enterprise
Windows Vista Ultimate
Windows Vista Home Premium
Windows Vista Home Basic
Windows Vista Enterprise
Windows Vista Business
Windows Server 2008 Standard
Windows Server 2008 Standard without Hyper-V
Windows Server 2008 Datacenter
Windows Server 2008 Datacenter without Hyper-V
Windows Server 2008 Enterprise
Windows Server 2008 Enterprise without Hyper-V
Windows Server 2008 Web
Windows 7 Web
Windows 7 Home Basic
Windows 7 Home Premium
Windows 7 Enterprise
Windows 7 Ultimate
Windows 7 Professional
Windows 2008 R2 Web
Windows 2008 R2 Standard
Windows 2008 R2 Enterprise
Windows 2008 R2 Datacenter
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows 8
Windows 8 Professional
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows 8.1
Windows 8.1 Professional

SQL Server 2008 Workgroup

Windows XP Professional
Windows Server 2003 SP2 Standard
Windows Server 2003 SP2 Datacenter
Windows Server 2003 SP2 Enterprise
Windows Vista Ultimate
Windows Vista Home Premium
Windows Vista Home Basic
Windows Vista Enterprise
Windows Vista Business
Windows Server 2008 Web
Windows Server 2008 Standard
Windows Server 2008 Standard without Hyper-V
Windows Server 2008 Datacenter
Windows Server 2008 Datacenter without Hyper-V
Windows Server 2008 Enterprise
Windows Server 2008 Enterprise without Hyper-V
Windows 7 Ultimate
Windows 7 Enterprise
Windows 7 Professional
Windows Server 2008 R2 Web
Windows Server 2008 R2 Foundation Server
Windows Server 2008 R2 Standard
Windows Server 2008 R2 Enterprise
Windows Server 2008 R2 Datacenter
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows 8
Windows 8 Professional
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows 8.1
Windows 8.1 Professional

SQL Server 2008 Web

Windows XP Professional
Windows Server 2003 SP2 Standard
Windows Server 2003 SP2 Datacenter
Windows Server 2003 SP2 Enterprise
Windows Vista Ultimate
Windows Vista Enterprise
Windows Vista Business
Windows Server 2008 Web
Windows Server 2008 Standard Windows Server 2008 Standard without Hyper-V
Windows Server 2008 Datacenter
Windows Server 2008 Datacenter without Hyper-V
Windows Server 2008 Enterprise
Windows Server 2008 Enterprise without Hyper-V
Windows Server 2008 R2 Web
Windows Server 2008 R2 Standard
Windows Server 2008 R2 Enterprise
Windows Server 2008 R2 Datacenter
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation

SQL Server 2008 Express

Windows Server 2003
Windows Server 2003 SP2 Standard
Windows Server 2003 SP2 Datacente
Windows Server 2003 SP2 Enterprise
Windows Vista Ultimate
Windows Vista Home Premium
Windows Vista Home Basic
Windows Vista Enterprise
Windows Vista Business
Windows Server 2008 Web1
Windows Server 2008 Standard
Windows Server 2008 Standard without Hyper-V
Windows Server 2008 Datacenter
Windows Server 2008 Datacenter without Hyper-V
Windows Server 2008 Enterprise
Windows Server 2008 Enterprise without Hyper-V
Windows 7 Ultimate
Windows 7 Home Basic
Windows 7 Home Premium
Windows 7 Enterprise
Windows 7 Professional
Windows Server 2008 R2 Web
Windows Server 2008 R2 Foundation Server1
Windows Server 2008 R2 Standard
Windows Server 2008 R2 Enterprise
Windows Server 2008 R2 Datacenter
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows 8
Windows 8 Professional
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows 8.1
Windows 8.1 Professional

Note: SQL Server 2008 support on Windows Server 2003 R2 is the same as support on Windows Server 2003 SP2.



SQL Server 2005 - Operating System Requirements
SQL Server 2005 Enterprise Edition

Windows 2000 Server SP4
Windows 2000 Advanced Server SP4
Windows 2000 Datacenter Edition SP4
Windows Server 2003 Server SP1
Windows Server 2003 Standard Edition SP1
Windows Server 2003 Enterprise Edition SP1
Windows Server 2003 Datacenter Edition SP1
Windows Small Business Server 2003 Standard Edition SP1
Windows Small Business Server 2003 Premium Edition SP1
Windows Server 2008
Windows Server 2008 Enterprise
Windows Server 2008 Data Center
Windows Server 2008 Web Edition
Windows Server 2008 Standard
Windows Server 2008 Enterprise
Windows Server 2008 Data Center
Windows Server 2008 Web

SQL Server 2005 Developer Edition

Windows 2000 Professional Edition SP4
Windows 2000 Server SP4
Windows 2000 Advanced Server SP4
Windows 2000 Datacenter Edition SP4
Windows XP Home Edition SP2
Windows XP Professional Edition SP2
Windows XP Media Edition SP2
Windows XP Tablet Edition SP2
Windows Server 2003 Server SP1
Windows Server 2003 Enterprise Edition SP1
Windows Server 2003 Datacenter Edition SP1
Windows Small Business Server 2003 Standard Edition SP1
Windows Small Business Server 2003 Premium Edition SP1
Windows Vista Home Basic Edition
Windows Vista Home Premium Edition
Windows Vista Ultimate Edition
Windows Vista Business Edition
Windows Vista Enterprise Edition
Windows Server 2003 Standard Edition SP1
Windows Server 2003 Datacenter Edition SP1
Windows Server 2003 Enterprise Edition SP1
Windows XP Professional 2003
Windows Vista Home Basic Edition
Windows Vista Home Premium Edition
Windows Vista Ultimate Edition
Windows Vista Business Edition
Windows Vista Enterprise Edition
Windows Server 2008
Windows Server 2008 Enterprise
Windows Server 2008 Data Center
Windows Server 2008 Web Edition
Windows Server 2008 Standard
Windows Server 2008 Enterprise
Windows Server 2008 Data Center
Windows Server 2008 Web

SQL Server 2005 Standard Edition

Windows 2000 Professional Edition SP4
Windows 2000 Server SP4
Windows 2000 Advanced Server SP4
Windows 2000 Datacenter Edition SP4
Windows XP Professional Edition SP2
Windows XP Media Edition SP2
Windows XP Tablet Edition SP2
Windows Server 2003 Server SP1
Windows Server 2003 Enterprise Edition SP1
Windows Server 2003 Datacenter Edition SP1
Windows Small Business Server 2003 Standard Edition SP1
Windows Small Business Server 2003 Premium Edition SP1
Windows Vista Ultimate Edition
Windows Vista Business Edition
Windows Vista Enterprise Edition
Windows Server 2003 Standard Edition SP1
Windows Server 2003 Datacenter Edition SP1
Windows Server 2003 Enterprise Edition SP1
Windows XP Professional 2003
Windows Vista Ultimate Edition
Windows Vista Business Edition
Windows Vista Enterprise Edition
Windows Server 2008
Windows Server 2008 Enterprise
Windows Server 2008 Data Center
Windows Server 2008 Web Edition
Windows Server 2008 Standard
Windows Server 2008 Enterprise
Windows Server 2008 Data Center
Windows Server 2008 Web

SQL Server 2005 Workgroup Edition

Windows 2000 Professional Edition SP4
Windows 2000 Server SP4
Windows 2000 Advanced Server SP4
Windows 2000 Datacenter Edition SP4
Windows XP Professional Edition SP2
Windows XP Media Edition SP2
Windows XP Tablet Edition SP2
Windows Server 2003 Server SP1
Windows Server 2003 Enterprise Edition SP1
Windows Server 2003 Datacenter Edition SP1
Windows Small Business Server 2003 Standard Edition SP1
Windows Small Business Server 2003 Premium Edition SP1
Windows Vista Ultimate Edition
Windows Vista Business Edition
Windows Vista Enterprise Edition
Windows Server 2003 Standard Edition SP1
Windows Server 2003 Datacenter Edition SP1
Windows Server 2003 Enterprise Edition SP1
Windows XP Professional 2003
Windows Vista Ultimate Edition
Windows Vista Business Edition
Windows Vista Enterprise Edition
Windows Server 2008
Windows Server 2008 Enterprise
Windows Server 2008 Data Center
Windows Server 2008 Web Edition
Windows Server 2008 Standard
Windows Server 2008 Enterprise
Windows Server 2008 Data Center
Windows Server 2008 Web

SQL Server 2005 Express Edition

Windows 2000 Professional Edition SP4
Windows 2000 Server SP4
Windows 2000 Advanced Server SP4
Windows 2000 Datacenter Edition SP4
Windows XP Embedded SP2 Feature pack 2007
Windows Embedded for Point of Service
Windows XP Home Edition SP2
Windows XP Professional Edition SP2
Windows XP Media Edition SP2
Windows XP Tablet Edition SP2
Windows Server 2003 Server SP1
Windows Server 2003 Enterprise Edition SP1
Windows Server 2003 Datacenter Edition SP1
Windows Server 2003 Web Edition SP1
Windows Small Business Server 2003 Standard Edition SP1
Windows Small Business Server 2003 Premium Edition SP1
Windows Vista Home Basic Edition
Windows Vista Home Premium Edition
Windows Vista Ultimate Edition
Windows Vista Business Edition
Windows Vista Enterprise Edition
Windows Server 2003 Standard Edition SP1
Windows Server 2003 Datacenter Edition SP1
Windows Server 2003 Enterprise Edition SP1
Windows XP Professional 2003
Windows Vista Home Basic Edition
Windows Vista Home Premium Edition
Windows Vista Ultimate Edition
Windows Vista Business Edition
Windows Vista Enterprise Edition
Windows Server 2008
Windows Server 2008 Enterprise
Windows Server 2008 Data Center
Windows Server 2008 Web Edition
Windows Server 2008 Standard
Windows Server 2008 Enterprise
Windows Server 2008 Data Center
Windows Server 2008 Web

Note: SQL Server 2005 support on Windows Server 2003 R2 is the same as support on Windows Server 2003 SP1.

Read more on this article...

Monday, May 23, 2016

SQL Server 2008 R2 Service Pack Installation Fails - Element not found. (Exception from HRESULT: 0x80070490)

In case you are trying to install a SQL Server 2008 R2 service pack in a clustered SQL Server environment and the installation fails reporting something similar to the below:

Final result: The patch installer has failed to update the shared features. To determine the reason for failure, review the log files.
Exit code (Decimal): -568706566
Exit facility code: 1562
Exit error code: 14842
Exit message: Element not found. (Exception from HRESULT: 0x80070490)
Start time: .....
End time: .....
Requested action: Patch

Then, the first thing you can do is to check if there are any disks in the cluster that are offline. If this is the case, you need to bring the disks back online prior to trying again.

As a side note, for the recommended practice of patching clustered SQL Server instances you can read this article.
Read more on this article...

Thursday, April 21, 2016

Entity Framework: Code First

A few days ago we had our second user group meeting for 2016 at Cyprus .NET User Group and it was all about data access.

I was the speaker at the event and I presented Entity Framework and more specifically the "Code First" development approach.

Entity Framework is an Object/Relational Mapping (ORM) Framework that treats data as Domain-Specific Objects. Entity Framework supports a variety of DBMSs.

The purpose of Entity framework is to simplify the data-access required when developing database applications.

In order to work with Entity Framework you need:
In order to install Entity Framework, right after you start a new project in Visual Studio, you can add it either via the GUI of NuGet Manager or via Package Manager Console with the command: Install-Package EntityFramework.

Figure 1: NuGet Package Manager.


Figure 2: Package Manager Console - Syntax for Installing Entity Framework.






Code First was first introduced in Entity Framework 4.1 and it follows the Domain Driven Design (DDD) paradigm. It is one of the three available development approaches in Entity Framework. The main idea is to create Domain Classes based on which the database can be created in the background.

In Entity Framework you can query data with any of the below methods:
  • LINQ to Entities
    • Method syntax
    • Query syntax
  • Entity SQL 
  • Native SQL


Figure 3: Component Stack when Using Entity Framework. Source: MSDN.
























For more information you can visit the below resources:

Read more on this article...

Wednesday, April 13, 2016

SQL Server 2016: Dynamic Data Masking

Dynamic Data Masking is another data protection feature in SQL Server 2016. It limits sensitive data exposure by masking it to non-privileged users. The masking rules are applied in the query results and you designate how much of the sensitive data to reveal and to who. The Dynamic Data Masking feature provides four types of masking methods:
  • Default: Full masking according to the data types of the designated fields.
  • Email: Masking method which exposes the first letter of an email address and the constant suffix ".com", in the form of an email address. . aXXX@XXXX.com.
  • Custom String: Masking method which exposes the first letter of an email address and the constant suffix ".com", in the form of an email address. . aXXX@XXXX.com.
  • Random: A random masking function for use on any numeric type to mask the original value with a random value within a specified range.
A simple example of Dynamic Data Masking is to display only the last 4 digits of a phone number to the support personnel of an IT Department or don't show the email address to unauthorized users, etc.

Now let's proceed with a demo in order to see some examples of using Dynamic Data Masking.

/*
Demo: Dynamic Data Masking
*/

--Create a sample database
IF DB_ID('SampleDB_DataMasking') IS NULL
BEGIN
CREATE DATABASE SampleDB_DataMasking;
END
GO

--Use sample database
USE SampleDB_DataMasking;
GO

--Create sample table
CREATE TABLE tblEmployeesMasking
    (
      empName VARCHAR(250) ,
      empPhoneNo VARCHAR(20) ,
      empEmailAddress VARCHAR(100)
    );
GO

--Populate sample table with data
INSERT  INTO dbo.tblEmployeesMasking
VALUES  ( 'John Clarck', '545-5478-1234', 'john.clark@example.com' ),
        ( 'Kostas Andreou', '878-8888-5678', 'kostas.andreou@example.com' ),
        ( 'Wei Xing', '673-5123-9101', 'wei.xing@example.com' );

--Check records (unmasked)
SELECT * FROM tblEmployeesMasking



Output










--Now let's mask the email address
ALTER TABLE dbo.tblEmployeesMasking
ALTER COLUMN empEmailAddress VARCHAR(100) MASKED WITH (FUNCTION = 'email()');

Let's check again the records of the table, this time using a non-sysadmin user.

--Create user (non-sysadmin) with GRANT SELECT permission on the sample table
CREATE USER TestUser WITHOUT LOGIN;
GRANT SELECT ON dbo.tblEmployeesMasking TO TestUser;
GO

--Retrieve the table contents by running the query with TestUser
EXECUTE AS USER = 'TestUser';
SELECT * FROM dbo.tblEmployeesMasking;
GO


Output













As you can see in the above screenshot, the column's 'empEmailAdress' values are presented as masked.
OK. So now let's switch the execution context back our original login's context in order to proceed with masking the 'empPhoneNo' column:

REVERT;
GO

--Let's mask the phone number with default
ALTER TABLE dbo.tblEmployeesMasking
ALTER COLUMN empPhoneNo VARCHAR(100) MASKED WITH (FUNCTION = 'default()');

Let's check the records of the table using a non-sysadmin user.

--Retrieve the table contents by running the query with TestUser
EXECUTE AS USER = 'TestUser';
SELECT * FROM dbo.tblEmployeesMasking;
GO

Output:











Now you can see that the column 'EmpPhoneNo' is also masked.
The next test is to remove the 'empPhoneNo' column's masking and set a custom masking function:

REVERT;
GO

--Remove masking 
ALTER TABLE dbo.tblEmployeesMasking
ALTER COLUMN empPhoneNo VARCHAR(100) 

--Let's mask the phone number with custom function
ALTER TABLE dbo.tblEmployeesMasking
ALTER COLUMN empPhoneNo VARCHAR(100) MASKED WITH (FUNCTION = 'partial(3,"-XXXX-XX",2)');


Let's check the records of the table using a non-sysadmin user.

--Retrieve the table contents by running the query with TestUser
EXECUTE AS USER = 'TestUser';
SELECT * FROM dbo.tblEmployeesMasking;
GO

Output:











As you can see from the above screenshot, now the empPhoneNo column is masked based on our custom masking function.
Last, if you want to allow 'TestUser' to be able to see the masked data, you can grant her or him the UNMASK permission:

REVERT;
GO

-- Granting the UNMASK permission
GRANT UNMASK TO TestUser;
GO

Let's check the records of the table within the context of 'TestUser':

--Retrieve the table contents by running the query with TestUser
EXECUTE AS USER = 'TestUser';
SELECT * FROM dbo.tblEmployeesMasking;
GO

Output:











Because TestUser was granted the UNMASK permission, it was possible to see the unmasked data.

Similarly, if REVOKE the UNMASK permission, TestUser will only see the masked data:


















Dynamic Data Masking is a significant feature in SQL Server 2016 and Azure SQL Database. As you can see from the above examples it is very easy to use it and it can really help you when it comes to Security and Compliance regarding yours or your organization's most valuable asset: the data.


References: 
MSDN Library Article: What's New in SQL Server 2016 Release Candidate (RC2)
MSDN Library Article: Dynamic Data Masking
Microsoft Azure Document: Get started with SQL Database Dynamic Data Masking (Azure Portal)

See also...
SQL Server 2016 Top Features


--
My Latest Projects:

Read more on this article...

Friday, April 1, 2016

I'm a Microsoft Data Platform MVP for the 8th Year!

Just got the magic email from Microsoft saying:











Dear Artemakis Artemiou,

Congratulations! We are pleased to present you with the 2016 Microsoft® MVP Award! This award is given to exceptional technical community leaders who actively share their high quality, real world expertise with others. We appreciate your outstanding contributions in Data Platform technical communities during the past year.

...

The Microsoft MVP Award provides us the unique opportunity to celebrate and honor your significant contributions and say "Thank you for your technical leadership."
-------------------------------------------
This is my 8th year as a Microsoft Data Platform MVP and I feel very honored receiving this prestigious award for another year. 
Interacting with the Data Platform technical communities all over the world is something magic. The endless technical discussions, the quest for knowledge and solutions to complex problems, the excitement for new technologies, events, books, blogs, social networks, ..., everything, it is something special.

I have been working with SQL Server for over than 15 year now and hey, I'm just loving it! Working with and constantly researching the most powerful Data Platform in the world for so many years can only offer you deep knowledge about a large set of technologies, that all together converge and form this amazing Data Platform known to all of us as SQL Server and Azure SQL Database.

A huge thank you to all of you, my fellow SQL Server community members for having these great technical discussions through all online and offline channels. It is these discussions that make us better in what we do.

I would also like to thank Microsoft for supporting the technical communities all over the globe so actively and continuously. Also, a special thank you to the local Microsoft Office at Cyprus for supporting our community activities at the Cyprus .NET User Group.

I would also like to thank the SQL Server and other involved teams at Microsoft for implementing this amazing Data Platform vision and for having all those great discussions with us.

Furthermore, I would like to thank the CEE MVP Community Team for all its valuable support throughout each MVP Award year.

On top of everything, a huge thank you to my beautiful wife and daughter for their true support, love and understanding for all those endless hours I spent away from them working on all those "community stuff" :) Without your support, you know it would just not feel right. I love you and thank you from the depths of my heart loves! Once again, this award is dedicated to you!

The journey continues. So many technologies, all part of this amazing Data Platform: In-Memory OLTP, SQL Server on Linux, Advanced Analytics with SQL Server R Services, and the list goes on.

Once again, my commitment to all of you is that I will continue sharing with you my knowledge on SQL Server and related technologies via my blog, online and offline events, my books, social networks, my technical papers on different journals, and any other available channel!


Read more on this article...

Wednesday, March 30, 2016

SQL Server 2016: The STRING_SPLIT Function

A long-awaited string function added to SQL Server 2016 is STRING_SPLIT. As the name implies, this function splits the given character expression using the separator set by user.

Let's see some examples of using the STRING_SPLIT function.


--
--Example #1
--
DECLARE @string AS VARCHAR(250);
SET @string = '1-2-3-4-5-6-7-8-9-10';

SELECT  Value
FROM STRING_SPLIT(@string, ',');

--Output:


























--
--Example #2
--
CREATE TABLE #test
    (
      id INT ,
      productsPurchased VARCHAR(250)
    );

INSERT  INTO #test
VALUES  ( 1, 'product1, product2, product3' );

SELECT  id ,
        LTRIM(t2.value)
FROM    #test t
        CROSS APPLY STRING_SPLIT(productsPurchased, ',') t2;

--Output:





























--
--Example #3
--
CREATE TABLE #CSVHeaders ( headers VARCHAR(MAX) );

INSERT  INTO #CSVHeaders
VALUES  ( 'header1,header2,header3,header4,header5,header6,header7,header8,header9,header10,header11,header12,header13,header14,header15' );

SELECT  LTRIM(value) AS HeaderName
FROM    #CSVHeaders
        CROSS APPLY STRING_SPLIT(headers, ','); 

--Output:































As you can see, STRING_SPLIT is a very handy new string function in SQL Server 2016. It allows you to easily manipulate text and organize it by parsing it using separators.

For more info, please visit MSDN Books Online.

Read more on this article...