8 Replies Latest reply on Nov 11, 2010 4:54 PM by spilon

    PI AF, CLR and SQL servers in enterprises

    Børre Heggernes

      Hi there,

       

      I'm looking for inputs on PI AF and the use of CLR in MS SQL.

       

      Our experience is we have so far met 2 DBAs at our customer sites that are not willing to enable CLR on their MSSQL database hotel (cluster), and espescially the UNSAFE option. This means I will not be able to use PI Webparts and PI OLEDB Enterprise.

       

      Now, as it happens, I kinda want to use those products. So what are my options?

      1. SQL Express, sure but it has it's limitations and also the customer has a policy of no SQL express installs.
      2. Another SQL server machine(which is probably what we will end up doing, virtualized)

      Other options I'm missing? Whish there was a PM for data access or something around here ;-) What are others doing? It cannot be the DBAs I'm talking to who are the only ones not willing to enable this? Seems to be not a good fit with large enterprises.

        • Re: PI AF, CLR and SQL servers in enterprises
          pcombellick

          Børre,

           

          Do you have a list of features of SQL Server that are unacceptable to customers?

           

          PIOLEDB Enterprise uses the .NET CLR feature of SQL Server to perform user authorization on AF objects via the Authz* WIN32 API.

           

          If we provided the customer with a detailed description of this functionality, would it convince the customer to allow the use of this feature?

           

          Regards,

           

          Paul

           

          AF Dev Team

            • Re: PI AF, CLR and SQL servers in enterprises
              Børre Heggernes

               

               

              It's the scripts in PISYSOLEDB he refuses to install based on the following :

               

              (quoting, transleated by me)

               

              - It enables CLR in UNSAFE mode. This is not recommended from Microsoft. Potential security issue

               
              CREATE ASSEMBLY [OSIsoft.Authorization]
                                    asxasxasx
              WITH PERMISSION_SET = UNSAFE
               
               
              GRANT UNSAFE ASSEMBLY TO [OSIsoft.Authorization]'
              UNSAFE
              UNSAFE gives assemblies unrestricted access to resources, both within and outside SQL Server. Code that is running from within an UNSAFE assembly can call unmanaged code.
              Also, specifying UNSAFE allows for the code in the assembly to perform operations that are considered type-unsafe by the CLR verifier. These operations can potentially access memory buffers in the SQL Server process space in an uncontrolled manner. UNSAFE assemblies can also potentially subvert the security system of either SQL Server or the common language runtime. UNSAFE permissions should be granted only to highly trusted assemblies by experienced developers or administrators. Only members of the sysadmin fixed server role can create UNSAFE assemblies.
                • Re: PI AF, CLR and SQL servers in enterprises
                  pcombellick

                  It is ironic that our attempt to improve security, by using Windows authentication and authorization, is perceived as unsecure.  SQL Server does not implement a mechanism to provide row level security, so we implemented one using the WIN32 API.  Would it be "safer" if we did not use Windows authentication and authorization?   (We have identified a path to eliminate the use of SQL CLR in a future release.)  

                   

                  I have had conversations with customer DBAs regarding how AF and PIOLEDB Enterprise use SQL Server.  Some don't like how we named the database files. 

                   

                  A few don't like our install kit requiring SysAdmin privilege and would prefer a completly manual installation mechanism.  Most are the exact opposite and require a totally automatic installation mechanism.  (We are working to satisfy both ends of this spectrum.)

                   

                  Some don't like extended stored procedures (xp_* especially XP_CMDSHELL), OLE Automation, SQL Mail, etc.  (None of these features are in use by AF 2.2).

                   

                  A couple of DBAs like to review all of the AF SQL logic, which is several MB of SQL.

                   

                  Some DBAs refuse modifications to master or msdb or model database.  (AF 2.2 does not modify these system databases directly).

                   

                  Other than this SQL CLR issue, are you aware of other features of SQL Server that are perceived as unacceptable by your customers?

                   

                  Regards,

                   

                  Paul

                    • Re: PI AF, CLR and SQL servers in enterprises
                      Børre Heggernes

                      yes, it's ironic. But I guess we can argue all night about how good/safe/trusted partner/supplier we are, but as long as we open the door to a potential risk they are reluctant, or in this case plain unwilling, to install on their enterprise SQL servers.

                       

                      Other than this I have not heard any complaints, except collations failing for Danish/Norwegian, but this we can work around easily. Seems DBAs appreciate the manual install option, beeing able to review scripts. They also like the quality of OSIsoft documentation, allthough the description of CLR was a little hidden