mercoledì 22 febbraio 2017

Configure SQL Server 2014 Database Mail using Office 365 SMTP

Welcome to a new infra post… Yes, I’m not a system engineer and neither I wish to be it, but until it comes to press Next, Next, Next, Accept, End… I’m a very expert!!!

Well, in this post I’ll describe how to configure SQL Server 2014 Database Mail in order to send mail message using the Office365 default SMTP Server.

Two years ago I wrote a nice post where I describe how to send mail using this SMTP thanks to the .Net SMTPClient class… Have a look at that…

Now, I’ll apply this know how to configure SQL Server Database Mail.

From SQL Server Management Studio, connect to your SQL server and choose, under the Management node, Database Mail.



and follow the instructions shown by the screenshot below.




Click on Add Button: you must insert the following paramenter, using an email address with send mail permission for the Office 365 SMTP.







And now let test this configuration...



And here the result...


Good job infra man!!!

giovedì 16 febbraio 2017

Caps Lock, Scroll Lock and Num Lock Free Indicator

Finally, the killer app!!!

I’ve got a Lenovo S430 laptop, a very nice laptop which works with me for 3 years.

This pc has a little problem: it has no a CAPS LOCK indicator.

The solution is to install a Lenovo tool (Lenovo Power Management) that display an icon in the bottom left area of my monitor.

There is a little problem: this app does not work on my Windows 10 Enterpirse, Version 1607, Build 14393.693: when I try to install it, I get the error “Lenovo Power Manager doesn’t work on this version of Windows. Learn more” inside the notification area.



Then… Since I’m (I was) a developer, I thought: Why I cannot create it by myself?
Then…

With an handful of code lines, I create a simple .Net Application called CapsLockDetector (what fantasy) that displays inside the Try Bar, near the clock, 3 different icons:



Caps Lock



Num Lock



Scroll Lock


If one of those keys is pressed.

The prerequisite to use this nice software is the .Net Framework 4.6.1

This is a no install software, you need to copy the executable file inside a folder on your file system and then run it.

If you want, you can add it to the AutoRun registry key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Run
In order to start the program each time the PC is started.

Then, you can also tell to Windows to always show this icons on the try bar following, for example, the instructions on this post


Here the result:

video

You can download it for free from this URL:

THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES WITH
REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY
AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY SPECIAL, DIRECT,
INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM
LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE
OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR
PERFORMANCE OF THIS SOFTWARE.



venerdì 10 febbraio 2017

Free OCR using Microsoft Cognitive Service - Computer Vision

Do you need a free OCR? Are you a developer? Make it by yourself!!!

Using Microsoft Cognitive Service – Computer Vision, you can easily recognize the text present inside an image and convert it to a text.


All you need is to require a subscription key using this url


and then create a very simple console application which send the image to Azure Cognitive Service and wait for a result.
The first thing is to add a the Microsoft.ProjectOxford.Vision Nuget Package like shown in the picture below.



In this example, I take all the images stored inside several folder on my desktop which name ends with “_crop” and then send the stream of the image to Microsoft Service.

The result of the conversion is appended to a text file.

Between the line some useful comments.

using System;
using System.Text;

namespace Ocr
{
    class Program
    {
        static void Main(string[] args)
        {
            //Creating client passing my Subscription Key
            //You can also store the key ibto your app config
            Microsoft.ProjectOxford.Vision.VisionServiceClient clnt = new Microsoft.ProjectOxford.Vision.VisionServiceClient("15f28d43ed5744418888xxxxxxxxxxxx");

            //Getting all files in the asd folder on my desktop
            //Whose name ends with crop
            var files = System.IO.Directory.GetFiles(@"c:\users\s.russo\desktop\asd", "*_crop.jpg", System.IO.SearchOption.AllDirectories);

            //For each file found I call the RecognizeTextAsync method of Azure Cognitive Services
            foreach (var file in files)
            {
                Console.WriteLine(file);

                //Get file stream from image file
                var filestream = new System.IO.MemoryStream(System.IO.File.ReadAllBytes(file));

                //Call the RecognizeTextAsync method (Async)
                var t = clnt.RecognizeTextAsync(filestream, Microsoft.ProjectOxford.Vision.Contract.LanguageCodes.Italian);

                //Wait for the result
                t.Wait();

                //Retrieve a string from the result
                string s = showRetrieveText(t.Result);
                s += "\r\n******************************************************\r\n";

                //Append the conversion result to a text file
                System.IO.File.AppendAllText(@"c:\users\s.russo\desktop\asd\text.txt", s);

                Console.WriteLine(s);
            }

        }

        /// <summary>
        /// Retrieve all text in the conversion result
        /// Copied from Microsoft Samples
        /// </summary>
        /// <param name="results"></param>
        /// <returns></returns>
        private static string showRetrieveText(Microsoft.ProjectOxford.Vision.Contract.OcrResults results)
        {
            StringBuilder stringBuilder = new StringBuilder();

            if (results != null && results.Regions != null)
            {
                foreach (var item in results.Regions)
                {
                    foreach (var line in item.Lines)
                    {
                        foreach (var word in line.Words)
                        {
                            stringBuilder.Append(word.Text);
                            stringBuilder.Append(" ");
                        }

                        stringBuilder.AppendLine();
                    }

                    stringBuilder.AppendLine();
                }
            }

            return stringBuilder.ToString();
        }

    }
}


mercoledì 8 febbraio 2017

Create in memory archive

Here a simple example of how to create an “in memory” zip file using c# and .Net Framework.

My real need is to send a mail attachment containing a lot of files stored into an Azure Blob Storage container, without having to write anything on the Web Server file system.

Here the code…

You have to add a reference to:
  • System.IO.Compression
  • System.IO.Compression.FileSystem


        /// <summary>
        /// Return a Memory Stream which rapresents the zipped archive
        /// of all Memory Stream passed as parameters
        /// </summary>
        /// <param name="files"></param>
        /// <returns></returns>
        private static MemoryStream createZip(List<MyFileInfo> files)
        {
            using (var memoryStream = new MemoryStream())
            {
                using (var archive = new ZipArchive(memoryStream, ZipArchiveMode.Create, true))
                {
                    foreach (var file in files)
                    {
                        var entryFile = archive.CreateEntry(file.FileName);
                        using (var entryStream = entryFile.Open())
                        {
                            file.FileContent.Seek(0, SeekOrigin.Begin);
                            file.FileContent.CopyTo(entryStream);
                        }
                    }
                }
                return memoryStream;
            }
        }
    

    /// <summary>
    /// MyFileInfo class stores the file name and its content
    /// </summary>
    class MyFileInfo
    {
        public string FileName { get; set; }
        public MemoryStream FileContent { get; set; }
    }

martedì 7 febbraio 2017

The target principal name is incorrect. Cannot generate SSPI context

Another post a little ‘more’ infrastructural, which perhaps can also affect our cousins with screwdriver.

Scenario:
I’ve a little SharePoint farm composed by the following Hyper-V Virtual Machines:
  • SharePoint Server
  • SQL
  • Domain Controller
  • Workflow Manager
  • SMTP


Navigating my SharePoint central admin, I get an internal server error. Sifting in ULS I get a lot of error with the description:

The target principal name is incorrect. Cannot generate SSPI context.

To exclude that this is a SharePoint problem, I create a simple .Net console application which try to open a .Net SQLClient connection to a database hosted by my SQL machine.

This application works fine running inside the SQL Server Machine, while failed with the same error (The target principal name is incorrect. Cannot generate SSPI context.) from any of the other machines belonging my domain.

With my dear friend Bing (lie) I find a lot of articles that tell about a Kerberos problem and that I can resolve my problem using SETSPN command.

SETSPN command Reads, modifies, and deletes the Service Principal Names (SPN) directory property for an Active Directory service account. You use SPNs to locate a target principal name for running a service. You can use setspn to view the current SPNs, reset the account's default SPNs, and add or delete supplemental SPNs.


Using SETSPN -l [MachineName] I can retrieve the Service Principal Names registered for my SQL machine.



Where the first 2 lines (MSSQLSvc/zSQL2014…)
Are right for my SQL instance: turning of the SQL service, the first 2 line will disappear; restarting this service, these lines appear again in the SPN List.

Then, the problem may not be here.
I tried to purge the Kerberos cache using the command Klist purge inside the machine which wants to connect to SQL Server…
Allows you to delete all the tickets of the specified logon session.
before creating a connection to my database, but nothing, always the same error.

Listing the avalilable tickets on this server using the Klist command, I notice that the ticket released for access to SQL Server is out of date, despite it was just released.



After a lot of tries, the idea of the year: check the date and time of the domain controller machine!!!

In fact, my domain controller has a wrong date, it is set to 12 days before.
Setting the right date and time and re purging the ticket cache using Klist Purge command, all worked fine for me!!!

Don’t ask me why domain controller date and time is wrong, but this solved the problem for me!!!
Just in time!!!


giovedì 19 gennaio 2017

Update SharePoint ECB Custom Action ScriptSrc

I created a SharePoint ECB Custom Action using the (legacy) declarative paradigm of the SharePoint solution, creating a no code sandboxed solution deployed into Office 365 SharePoint site collection.

<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <CustomAction Id="CommonMenu.Script"
            Location ="ScriptLink"
            ScriptSrc="~SiteCollection/Style Library/Scripts/common.js?version=2016.02.10" />
  <CustomAction Id="ProBookMenu.Script"
          Location ="ScriptLink"
          ScriptSrc="~SiteCollection/Style Library/Scripts/xxxxxxx.js?version=2016.02.10" />
  <CustomAction Id="CopyAOACCAMenu.Script"
            Location ="ScriptLink"
            ScriptSrc="~SiteCollection/Style Library/Scripts/Copyxxxx.js?version=2016.02.10" />
  <CustomAction Id="CopyAOACCAMenu"
                RegistrationType="ContentType"
                RegistrationId="0x0120D52000259111EF1E2D4F4287A1EAC35513EC79"
                Location="EditControlBlock"
                Sequence="100"
                Title="Copy xxx">
    <UrlAction Url="javascript:Copyxxx('{ListId}', {ItemId}, false)" />
  </CustomAction>
</Elements>

To avoid that users must press CTRL + F5 when my scripts are changed, I added at the end of the ScriptSrc location a fake parameter that I’ll change when I need to update the cached script version of client browsers.

In my case I added a fake parameter named version with the reference to the script date. It’s only a convention: I could also write a random string (e.g. CiaoMamma). The important is that the new script has a reference with a different parameter from the current script.

Well, all works fine since today when, after one year, we discovered a little bug (bugs do not exist: they are only unwanted features :) ) and I’ve to update my script.

To allow this, I changed my script inside my SharePoint Solution and then I should deploy again my WSP with the new script and the Custom Action Element file updated with a new version number.

In this case, custom action definition is in the same solution of all other custom SharePoint artifacts:
  • Site Fields
  • Content Types
  • List definitions
  • List Instances
  • Modules
  • ·    

Deploying this WSP means deploy again all the above object inside, in my case, a site collection with giga bytes of critical documents.

This operation, within SharePoint online, is a lottery!!!

Then I thought to create a very simple console application which update this parameter using CSOM (Client Side Object Model).

Using NuGet I added the SharePoint client DLL to my project (Microsoft.Sharepoint.2013.Client.16) and then I wrote this simple code.

Comments between the lines…

using Microsoft.SharePoint.Client;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Security;
using System.Text;
using System.Threading.Tasks;

namespace UpdateCustomAction
{
    class Program
    {
        static void Main(string[] args)
        {

            string siteUrl = "https://xxx.sharepoint.com/sites/xx-xx-xxxxxx";
            string pwd = "yyyyy";
            string userName = "sergio.russo@xxxxx.com";

            using (ClientContext clientContext = new ClientContext(siteUrl))
            {
                //Creating SPOnline Credentials
                SecureString securePassword = new SecureString();
                foreach (char c in pwd.ToCharArray()) securePassword.AppendChar(c);
                clientContext.Credentials = new SharePointOnlineCredentials(userName, securePassword);

                //Getting SPWeb
                Web web = clientContext.Web;
                var userCA = web.UserCustomActions;

                //Loading Web Custom Actions
                clientContext.Load(userCA);
                clientContext.ExecuteQuery();

                //Getting the list of all custom actions with use my script as ScriptSrc
                var copyJsActions = userCA.Where(u => (u.ScriptSrc != null) && (u.ScriptSrc.ToLower().StartsWith("~sitecollection/style library/scripts/copyxxx.js"))).ToList();

                foreach (var copyJsAction in copyJsActions)
                {
                    //Replecing the version parameter
                    string s = copyJsAction.ScriptSrc;
                    string s1 = s.Substring(0, s.LastIndexOf("?"));
                    string res = $"{s1}?version=2017.01.19";
                    copyJsAction.ScriptSrc = res;

                    //Update the custom action
                    copyJsAction.Update();
                    clientContext.ExecuteQuery();

                }
               
                if (copyJsActions.Count == 0)
                {
                    Console.WriteLine("Custom Action not found");

                }

                Console.ReadLine();

            }
        }
    }
}