BLOGS

ByHariharan Rajendran

Always Encrypted in SQL Server – Part 2

We can easily enable the encryption using wizard which you can check it here.

Disabling the encryption is also an easy task through wizard.

Steps to disable the encryption,

Step 1: Right Click on your database and choose Tasks->Encrypt Columns..

Step 2: Select next on the introduction page.

Step 3: When you are enter into column selection page, you can see the lock symbol on the columns which you enabled the encryption. Under encryption type, you can see the drop down values which includes “Plaintext”, “Deterministic” & “Randomized”. Choose the “Plaintext” and proceed next.

image

image

image

Step 4: Go through further steps and finish it.

Step 5: Now, run the select statement on table and you should see the result without an encryption.

 

We can define the “Encryption Key” & “Master key” Name manually.

Follow the steps to create custom names and to make use for encryption.

Step 1: Go to Security folder of your database.

image

Step 2: Expand the security folder and you should see the folder called “Always Encrypted Keys”. Expand the folder.

image

Step 3: You should see another two folders, Column Master Keys & Column Encryption Keys. Right click on Column Master Keys folder and choose “New Column Master Key..”

image

Step 4: Define the name of the master key and choose the key store where you want to store your key safely and click OK. Master will be created.

image

Step 5: Right click on Column Encryption Keys folder and choose “New Column Encryption Keys”

image

Step 6: Give the name and choose the master key from drop down. You should see the master key which you created in above step 4 and click OK.

image

Step 7: Now, go and create an encrypted column and you should see the option to choose your encryption key. Master key also will be applied automatically.

image

ByHariharan Rajendran

Always Encrypted SQL Server 2016

I have discussed “Always Encrypted” concept and covered steps to create Always encrypted columns through T-SQL Statement. Check column level encryption using Always Encrypted in SQL Server 2016

We can enable the encryption to the database table columns through wizard as well .

Before Always Encryption,

image

After Always Encryption,

image

Steps to encrypt the column,

Step 1: Right click your database where your tables are there to encrypt.

Step 2: Go to Tasks->Encrypt Columns… and choose.

image

Step 3: You will see the Introduction page, click Next

image

Step 4: Choose the columns from table and choose the encryption type. To know more about encryption type, visit here. The encryption key name will be created like “CEK_Auto”. It will keep extend like “CEK_Auto1”, …Auto2, etc. You can use the same encryption key for multiple columns.

image

image

Step 5: Once selected the required options, Next option will be enabled and click Next

image

Step 6: Master Key Configuration will be created for your database. It is an one time activity.

image

Step 7: Click Next and you can see two options,

  1. Generate PowerShell Script to run later – Basically this enabling the encryption should be done while doing database maintenance.
  2. Proceed to finish now

image

image

Step 8: Check the configuration in summary page and click next to proceed. The scripts will start run in the background and will encrypt the selected columns.

image

image

Step 9: Run the table and check the column values.

image

ByHariharan Rajendran

For Loop in R

In a real time, we will be in a position to handle the logic using loops. Loop concept is available in all programming language and also in R.

For example, if we want to generate first 200 prime numbers, we have to use loop to get the result.

In R, “for loop” is used to iterate over a vector.

Syntax

for (val in sequence) {

statement

}

In the above syntax, sequence is a vector which we can define and assign into an object and can use the object or we can directly place the vector with the expression. “val” will take the value of sequence and process in a loop. The statement will be triggered until the length of the sequence is completed.

Example,

x <- c(1,2,3,4,5,6,7,8,9,10)

count <- 0

for (val in x) {

if(val %% 2 == 0) count = count+1

}

print(count)

image

Using Next

We can use the word next inside the for loop and it will change the flow of the process. Check the below example,

for (i in 1:10) {

if (!i %% 2){

}

print(i)

}

image

If you run above script, I will get the result as 1,2,3,4,5,6,7,8,9,10 (All the values from the 1:10 sequence).

If you want to display only the odd number for the same sequence input then I can just add a “next” keyword inside if loop.

for (i in 1:10) {

if (!i %% 2){

next

}

print(i)

}

image

First “i” value 1 will go to if condition once the value reaches the keyword “next”. It will redirect to for loop again and it will come again for the second number i.e. 2 and it will continue up to 10.

ByHariharan Rajendran

If and Else function in R

I have discussed that how to use if and else statement in R in my previous article.

In a real time coding, we will be expecting a short form of code which should give the same expected result.

Vectors are basic building block of R Programming and will be used as input.

To apply the same if and else logic for the vector input, we can use if and else function which will give the same result but as a output vector.

 

Syntax:

Ifelse(test_expression,x,y)

The output of the function could be a vector. In the above syntax, test_expression is a condition which we can apply into the input vector and x is nothing but a custom value or expression, in logical it is called TRUE. If the condition is satisfied then x (TRUE) will display, you can replace x with any value. Here y is FALSE, if the test_expression is not satisfied then y will display.

Example,

number = c(3,5,7,10)
ifelse(number %% 2 == 0,”even”,”odd”)

image

Logically the result would be FALSE, FALSE, FALSE, TRUE.

ByHariharan Rajendran

If and Else statement in R

As we know, decision making is an important part for every programming. Almost all the projects make use of this decision making logic and sometimes this is very mandatory as well. Considering this importance, R language has if..else statement.

Probably we are in situation to run the set of codes when the condition is match. In that case, we can use if statement. It is very easy to use in R as like other programming language.

The General flow of “IF” statement,

image

Syntax:

if (test_expression) {
statement
}

Examples:

Example 1

x <- 10
if(x > 0){
print(“Positive number”)
}

 

Example 2

X<-weekdays(Sys.Date())

if(X == “Monday”)

{ print(“Today is Monday”)}

But in a real time scenario, sometimes we need to run the set of codes when the condition is not match. If that is the case then we need to use if and else statement.

image

Syntax:

if (test_expression) {
statement1
} else {
statement2
}

Examples:

Example 1

x <- -1
if(x > 0){
print(“Positive Number”)
} else {
print(“Negative number”)
}

Example 2

X<-weekdays(Sys.Date())

if(X == “Monday”)

{ print(“Today is Monday”)} else

{ print (“Today is not Monday”)}

We can use Nested if and else statement,

image

Syntax:

if ( test_expression1) {
statement1
} else if ( test_expression2) {
statement2
} else if ( test_expression3) {
statement3
} else
statement4

 

Examples:

Example 1

x <- 0
if (x < 0) {
print(“Negative number”)
} else if (x > 0) {
print(“Positive number”)
} else
print(“Zero”)

 

Example 2

X<-weekdays(Sys.Date())

if(X == “Monday”)

{ print(“Today is Monday”)} else if (X==”Tuesday”)

{ print (“Today is Tuesday”)} else if (X==”Wednesday”)

{ print (“Today is Wednesay”)} else if (X==”Thursday”)

{ print (“Today is Thursday”)} else if (X==”Friday”)

{ print (“Today is Friday”)} else if (X==”Saturday”)

{ print (“Today is Saturday”)} else

{print (“Today is Sunday”)}

ByHariharan Rajendran

Azure PowerShell Commandlets Issues

As usual, I was trying to use PowerShell Command line tool to manage my azure services. I used to prefer PowerShell as we have more control on it. This time, I got an error message, I was not able to run any Azure commandlets but I was able to add my azure account.

The error message is bit tricky and to solve the issue, I just follow closed and re-opened but still I got the same error message. I found that I got something issue with my saved credentials (token).

Error message,

Get-AzureSubscription : Failed to refresh token. AADSTS90002 : No Service namespace named ‘ ‘ was not found in the data store.
Trace ID:XXXXXXXXXX
Correlation ID: XXXXXXXXXXXXXXXXXX
TimeStamp: 2015-09-12 13:24:477 : The remote server returned an error : (400) Bad Request.

image

I followed the below steps to solve the issue,

Step 1: Explored the following folder in your system,  C:\Users\{User Name}\AppData\Roaming\Windows Azure Powershell.

Note: AppData folder will be in hidden by default and replace {User Name} into your system name.

Step 2: I saw the below files inside the folder and Just deleted all the files.

image

Step 3: Closed the PowerShell tool and reopen again.

Step 4: Added my azure account, this time, I was able to run all commands successfully.

ByDr. SubraMANI Paramasivam

Try SQL Server 2016 Release Candidate 3 in Azure Virtual Machine today

Don’t waste your monthly credits and if you have some you can always spin it up with Azure virtual machines to try the SQL Server 2016 RC3 Evaluation on Windows Server 2012 R2.

This is a great and quick fun as you don’t need to manually download the ISO file and keep installing them like I mentioned in this article with step by step instructions.

As you can see in the below Azure portal screen, I am using New => Virtual Machine and then clicked on “See all”, to see all available options and also to search on what I need to search for. The prebuilt images are already available for all of us to try and you can see the highlights that I have made.

SQL2016RC3_Azure_01

Then I got the below screen to confirm on what I was going to create and enable me to create it either with Resource Manager or Classis Deployment Model. In newer version Resource Manager is chosen as default for all deployment models.

SQL2016RC3_Azure_02

In below screen I have given basic options  with Name, username, my subscription, resource group, location. Once you have chosen all your settings till 5, then you will get your fully functional SQL Server 2016 RC 3 virtual machine in 10 to 15 minutes. How cool is that.

SQL2016RC3_Azure_03