mercredi 25 juin 2025

Enable TLS on sql server 2017: the powershell way

First create a certificate (thank you chatgpt):

$cert = New-SelfSignedCertificate `
  -CertStoreLocation "Cert:\LocalMachine\My" `
  -Subject "CN=MACHINE.DOMAINE.TLD" `
  -KeySpec KeyExchange `
  -KeyLength 2048 `
  -KeyExportPolicy Exportable `
  -NotAfter (Get-Date).AddYears(5) `
  -FriendlyName "Certificat SQL Server Dev" `
  -TextExtension @("2.5.29.19={critical}{text}")

One can now configure the SQL Server Instance:

SQL Server Configuration Manager > NetWork Configuration > Protocols > Certificate

Restart the instance - and watch it fail.

A certificate is born. A service stumbles. The path is clear: a key awaits its rightful bearer.

Translation: the instance does not have the right to access the private key of the certificate.

One important point here is that you get a CNG certificate and not a CSP one. With this in mind one can emit some more powershell commands (one in fact):

$cert.PrivateKey.Key
this will display the properties of the certificat and in particular: UniqueName

One can know that one is dealing with a CNG certificate because (at least) $cert.PrivateKey.CspKeyContainerInfo is empty. This was my first attempt to grant rights: it failed.

With UniqueName one can go to: C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys. Here one will find a file named after UniqueName. One can then access to the properties of the file and grant the access right to the sql server service account.

Et voilà

There are other ways:

  • icacls
  • winhttpcertcfg

The exposed way is efficient for SQL Server 2017 on Windows 10.

jeudi 1 novembre 2018

Enable TLS on sql server: the opennssl way

To do so you have to get a certificate meeting this requirements. The most important of them being:

  • The Common Name (CN) in the Subject or a DNS name in the Subject Alternate Name (SAN) of the certificate exactly matches the serverName value specified in the connection string or, if specified, the hostNameInCertificate property value.

The exact name depends on being on a domain or not.

For our example one uses the following configuration file (named san.cnf):

[req]
distinguished_name = req_distinguished_name
x509_extensions = v3_req
prompt = no
[req_distinguished_name]
CN = MYCOMPUTERNAME
[v3_req]
keyUsage = keyEncipherment,dataEncipherment
extendedKeyUsage = serverAuth
subjectAltName = @alt_names
[alt_names]
DNS.1 = MYCOMPUTERNAME
DNS.2 = MYCOMPUTERNAME.MYBUSINESSDOMAIN.MYSBUSINESSDOMAINTLD

Then one issues the following commands:

openssl req -x509 -newkey rsa:4096 -sha256 -keyout tlssql.key -out tlssql.crt -days 600 -config san.cnf

Then:

openssl pkcs12 -export -name “tls for sql by openssl” -out tlssql.pfx -inkey tlssql.key -in tlssql.crt

Then you can import the pfx file in your favorite hive. In my case I use IIS Manager to import the pfx file in the personnal hive.

Your "tls for sql by openssl" named certificcate is now available in the Sql Server Configuration Manager.

Et voilà...

A little further:

You may want to use an mmc console with the certificats component to browse among the known certificates. In such a case you will have the choice between:

  • User,
  • Service, or
  • Computer

In my experience the computer store is available for sql server running as a user account member of the users group.

jeudi 9 mars 2017

Reading an utf8 encoded xml file in C#: the stream solution

All is in the title.
using (Stream str = System.IO.File.Open(@"fileName.xml", FileMode.Open)) {
    XmlReaderSettings p = new XmlReaderSettings {
        DtdProcessing = DtdProcessing.Parse
    };
    using (XmlReader xr = XmlReader.Create(new StreamReader(str, Encoding.UTF8), p)) {
        XDocument xDoc = XDocument.Load(xr);
        // or
        //XElement xEl = XElement.Load(xr);
   
        //do your stuff here.
    }
}

mercredi 1 mars 2017

dotnet restore hangs then time out with 100000ms

Especially on W7/x64

I found the solution here: https://github.com/dotnet/cli/issues/1732.

For several people it seems to be related to network interface (VMWare, VirtualBox...). For me the following unleash the restore:
Using "procmon" I found something strange happening in the lock files in %TEMP%\NugetScratch. I deleted that folder and everything started to work again and CI builds now work perfectly.
Happy restore!

vendredi 22 juillet 2016

linux and wifi troubleshooting

this is not (at all) exhaustive

first test: is the harware enabled ?
sudo rfkill list
if the command shows:
Hard bloked: yes
this means that your wifi switch is on postion OFF.

mardi 8 mars 2016

Commands you must know for ACMESharp

To complete the QuickStart from https://github.com/ebekker/ACMESharp/wiki/Quick-Start you may encounter some troubles. Among them:

----- I -----
The given key was not present in the dictionary

The solution is given by one of the issue.

This his formally an extract/copy from https://github.com/ebekker/ACMESharp/issues/101. From this link one can read:

There are a few bookkeeping cmdlets you can run to make sure the parameters to the Complete-* cmdlet are valid:
  • Get-ACMEIdentifier -- with no arguments, this cmdlet will list all the current Identifiers you have in the Vault; if you give it an Identifier reference (e.g. sequence number, Alias or GUID) it will give you more details about that particular Identifier
  • Get-ACMEChallengeHandlerProfile -ListChallengeTypes -- this will return the list of all Challenge Types that are currently registered and available in the current PS session, e.g. dns-01, http-01
  • Get-ACMEChallengeHandlerProfile -ListChallengeHandlers -- this will return the list of all Challenge Handlers that are currently registered and available in the current PS session, e.g. manual, iis, awsRoute53, awsS3
  • Get-ACMEChallengeHandlerProfile -GetChallengeHandler iis -ParametersOnly -- this form of the cmdlet will list details about the parameters that must or may be specified for the named Challenge Handler type
With these cmdlets you can ensure that the parameters to your Complete-ACMEChallenge call are all correct, for example that the Identifier reference is valid. If you run these and everything seems to be specified correctly and you still get this error, then there might be a bug somewhere and we can trace through that.

----- II -----
Stuck in the pending status

The solution can be found here: http://stackoverflow.com/questions/35302044/letsencrypt-acmesharp-client-challenge-in-pending-state-for-over-an-hour

In short using the following command : (Update-ACMEIdentifier dns1 -ChallengeType http-01).Challenges should exhibit that one among three of the challenges is valid.

----- III -----
Cannot export PKCS12; Issuer certificate hasn't been resolved

The solution can be found here: https://github.com/ebekker/ACMESharp/issues/87.

In short an Update-ACMECertificate certAlias solves the problem.

lundi 14 septembre 2015

Nearest neighborS search by Binary Partition Tree

There are a lot of excellent references on Internet, one of them is:

The missing part: some code to play around. Let's try to make something generic. What to we need to make a class partitionable ?

    public interface IIsPartionableByBT {
        Int32 Id { get; }

        float BTX { get; }
        float BTY { get; }
        float BTZ { get; }

        float DistTo(IIsPartionableByBT c);
    }

    public delegate float BTCooProjector(IIsPartionableByBT coo);

We also need Cells/Nodes for the tree:

    public class MbtBSPNode<T> where T :  IIsPartionableByBT {
        public MbtBSPNode<T> Parent { get; set; }
        public T Cell { get; set; }
        public MbtBSPNode<T> Left { get; set; }
        public MbtBSPNode<T> Right { get; set; }

        public ICollection<MbtBSPNode<T>> Cells { get; set; }

        public UInt32 Depth { get { if (Parent == null) return 0; return Parent.Depth + 1; } }
    }

And the tree himself:

    public class MbtBSPTree<T> where T : class, IIsPartionableByBT {
        public MbtBSPNode<T> Root { get; private set; }

        public UInt16 SplitDepthBy { get; private set; }

        public UInt16 LimitDepthTo { get; private set; }

        public MbtBSPTree(IEnumerable<T> l, UInt16 splitDepthBy = 0, UInt16 limitDepthTo = 0) {
            if (SplitDepthBy == 0) {
                if (l.Min(x => x.BTZ) == l.Max(x => x.BTZ)) {
                    SplitDepthBy = 2;
                } else {
                    SplitDepthBy = 3;
                }
            } else {
                SplitDepthBy = splitDepthBy;
            }
            LimitDepthTo = limitDepthTo;

            Root = BuildBSPTree(l, 0);
        }

        public BTCooProjector DefProjector(UInt16 axis) {
            switch (axis) {
                case 0:
                    return (n) => n.BTX;
                case 1:
                    return (n) => n.BTY;
                case 2:
                    return (n) => n.BTZ;
                default:
                    throw new Exception("valeur de split de profondeur non gérée");
            }
        }

        public MbtBSPNode<T> BuildBSPTree(IEnumerable<T> l, UInt16 depth, MbtBSPNode<T> p = null) {
            if (l.Count() == 0)
                return null;
            UInt16 axis = (UInt16)(depth % SplitDepthBy);

            MbtBSPNode<T> n = new MbtBSPNode<T>();
            n.Parent = p;

            if (LimitDepthTo > 0 && depth == LimitDepthTo) {
                n.Cells = new List<MbtBSPNode<T>>();
                foreach (T cc in l) {
                    n.Cells.Add(new MbtBSPNode<T> { Cell = cc });
                }
            } else {
                Double med = Double.MinValue;
                List<T> ll = new List<T>();
                List<T> lr = new List<T>();

                BTCooProjector proj = DefProjector((UInt16)(depth % SplitDepthBy));
                med = l.Median(x => proj(x));
                foreach (T c in l) {
                    if (proj(c) < med) {
                        ll.Add(c);
                    } else {
                        if (proj(c) == med) {
                            if (n.Cell == null)
                                n.Cell = c;
                            else
                                ll.Add(c);
                        } else {
                            lr.Add(c);
                        }
                    }
                }

                if (depth <= 2) {
                    //this will lead to 8 threads
                    Task<MbtBSPNode<T>> tl = Task<MbtBSPNode<T>>.Factory.StartNew(() =>
                        BuildBSPTree(ll, (UInt16)(depth + 1), n));
                    n.Right = BuildBSPTree(lr, (UInt16)(depth + 1), n);
                    //synchro thread
                    n.Left = tl.Result;
                } else {
                    n.Left = BuildBSPTree(ll, (UInt16)(depth + 1), n);
                    n.Right = BuildBSPTree(lr, (UInt16)(depth + 1), n);
                }
            }

            return n;
        }

#if DEBUG
        public Int32 ScannedNodeNumber { get; set; }
#endif

        public IEnumerable<MbtBSPNode<T>> GetNearestNodes(T cel, float radius) {
#if DEBUG
            ScannedNodeNumber = 0;
#endif
            MbtBSPNode<T> current;
            Queue<MbtBSPNode<T>> q = new Queue<MbtBSPNode<T>>();
            q.Enqueue(Root);

            while (q.Count > 0) {
                current = q.Dequeue();
                BTCooProjector proj;

                if (current.Cell != null) {
#if DEBUG
                    ScannedNodeNumber++;
#endif
                    if (cel.DistTo(current.Cell) <= radius && cel.Id != current.Cell.Id) {
                        yield return current;
                    }

                    proj = DefProjector((UInt16)(current.Depth % SplitDepthBy));

                    if (proj(cel) - radius <= proj(current.Cell)) {
                        if (current.Left != null)
                            q.Enqueue(current.Left);
                    }
                    if (proj(cel) + radius > proj(current.Cell)) {
                        if (current.Right != null)
                            q.Enqueue(current.Right);
                    }
                }

                if (current.Cells != null) {
                    foreach (MbtBSPNode<T> cc in current.Cells) {
#if DEBUG
                        ScannedNodeNumber++;
#endif
                        if (cel.Id != cc.Cell.Id && cel.DistTo(cc.Cell) <= radius)
                            yield return cc;
                    }
                }

            }
        }

One can find a median algorithm in here: