Friday, July 17, 2020

SQL Server Reporting Services - Summing a Field Calculated in a Custom Code Assembly

I have a report that has a complex calculation being done in a custom code assembly that is attached to the report as a reference. The problem I ran into is trying to get a SUM on that calculation. I've written some custom code that you can input into the Code section on Report Properties.

Public Shared Value as Decimal=0.00
Public Shared ReturnValue as Decimal = 0.00
Public Shared Function GetValue(Item as Integer) as Integer
     value= value + Item
     return Item
End Function
Public Shared Function GetTotal()
     returnvalue = value
     value = 0
     return returnvalue
End Function


Then you just enclose the original expression inside:

=Code.GetValue(put expression here)

And then use this expression where you want the total:

=Code.GetTotal()

This code will also zero-out the 'Value' variable so that you can reuse it in a repeating group.

Friday, June 12, 2020

MacOS Google File Stream Error: drive file stream has encountered a problem and has stopped

I've spent too much of the last few days trying to figure out why I couldn't get Google File Stream running on a new account on a mac. I tried all of the recommended solutions with no luck so finally I decided to try to figure it out myself and I did get it working.

The problem I was seeing is that I was just getting an error every single time I tried to run the application. All I'd get was "drive file stream has encountered a problem and has stopped" and I couldn't change any preferences at all or do anything besides quit the app.

What fixed this issue for me (after trying everything else recommended online) was to uninstall Google File Stream, and then in finder show hidden files on the HD (cmd + shift + .) and go to Volumes and delete the Google Drive volume. Then I reinstalled Google File Stream, and once that was done it started working just fine the next time I fired the program up.

My guess is that the volume is created under the context of the user who installs the program, and if another account gets created it doesn't have permissions to access that volume. Purely a guess though. Anyway I hope that helps anyone who is running into this same issue.

Friday, January 31, 2020

.NET Core 3.1 Identity Database Script to Build Necessary DB Objects Without Using Migrate

Not everyone has the database access they need to use the whiz-bang database-migration tools when writing Razor apps with .NET Core 3.1. If this is you, and you either can't get the DBAs to give you that access, or you don't want to be running migrations on a database with other important data in it, then you can use this script directly in SQL Server to build the DB objects you'll need for the default .NET Core 3.1 Identity implementation.


      CREATE TABLE [AspNetRoles] (
          [Id] nvarchar(450) NOT NULL,
          [Name] nvarchar(256) NULL,
          [NormalizedName] nvarchar(256) NULL,
          [ConcurrencyStamp] nvarchar(max) NULL,
          CONSTRAINT [PK_AspNetRoles] PRIMARY KEY ([Id])
      );

      CREATE TABLE [AspNetUsers] (
          [Id] nvarchar(450) NOT NULL,
          [UserName] nvarchar(256) NULL,
          [NormalizedUserName] nvarchar(256) NULL,
          [Email] nvarchar(256) NULL,
          [NormalizedEmail] nvarchar(256) NULL,
          [EmailConfirmed] bit NOT NULL,
          [PasswordHash] nvarchar(max) NULL,
          [SecurityStamp] nvarchar(max) NULL,
          [ConcurrencyStamp] nvarchar(max) NULL,
          [PhoneNumber] nvarchar(max) NULL,
          [PhoneNumberConfirmed] bit NOT NULL,
          [TwoFactorEnabled] bit NOT NULL,
          [LockoutEnd] datetimeoffset NULL,
          [LockoutEnabled] bit NOT NULL,
          [AccessFailedCount] int NOT NULL,
          CONSTRAINT [PK_AspNetUsers] PRIMARY KEY ([Id])
      );

      CREATE TABLE [AspNetRoleClaims] (
          [Id] int NOT NULL IDENTITY,
          [RoleId] nvarchar(450) NOT NULL,
          [ClaimType] nvarchar(max) NULL,
          [ClaimValue] nvarchar(max) NULL,
          CONSTRAINT [PK_AspNetRoleClaims] PRIMARY KEY ([Id]),
          CONSTRAINT [FK_AspNetRoleClaims_AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [AspNetRoles] ([Id]) ON DELETE CASCADE
      );

      CREATE TABLE [AspNetUserClaims] (
          [Id] int NOT NULL IDENTITY,
          [UserId] nvarchar(450) NOT NULL,
          [ClaimType] nvarchar(max) NULL,
          [ClaimValue] nvarchar(max) NULL,
          CONSTRAINT [PK_AspNetUserClaims] PRIMARY KEY ([Id]),
          CONSTRAINT [FK_AspNetUserClaims_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [AspNetUsers] ([Id]) ON DELETE CASCADE
      );

      CREATE TABLE [AspNetUserLogins] (
          [LoginProvider] nvarchar(128) NOT NULL,
          [ProviderKey] nvarchar(128) NOT NULL,
          [ProviderDisplayName] nvarchar(max) NULL,
          [UserId] nvarchar(450) NOT NULL,
          CONSTRAINT [PK_AspNetUserLogins] PRIMARY KEY ([LoginProvider], [ProviderKey]),
          CONSTRAINT [FK_AspNetUserLogins_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [AspNetUsers] ([Id]) ON DELETE CASCADE
      );

      CREATE TABLE [AspNetUserRoles] (
          [UserId] nvarchar(450) NOT NULL,
          [RoleId] nvarchar(450) NOT NULL,
          CONSTRAINT [PK_AspNetUserRoles] PRIMARY KEY ([UserId], [RoleId]),
          CONSTRAINT [FK_AspNetUserRoles_AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [AspNetRoles] ([Id]) ON DELETE CASCADE,
          CONSTRAINT [FK_AspNetUserRoles_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [AspNetUsers] ([Id]) ON DELETE CASCADE
      );

      CREATE TABLE [AspNetUserTokens] (
          [UserId] nvarchar(450) NOT NULL,
          [LoginProvider] nvarchar(128) NOT NULL,
          [Name] nvarchar(128) NOT NULL,
          [Value] nvarchar(max) NULL,
          CONSTRAINT [PK_AspNetUserTokens] PRIMARY KEY ([UserId], [LoginProvider], [Name]),
          CONSTRAINT [FK_AspNetUserTokens_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [AspNetUsers] ([Id]) ON DELETE CASCADE
      );

      CREATE INDEX [IX_AspNetRoleClaims_RoleId] ON [AspNetRoleClaims] ([RoleId]);

      CREATE UNIQUE INDEX [RoleNameIndex] ON [AspNetRoles] ([NormalizedName]) WHERE [NormalizedName] IS NOT NULL;

      CREATE INDEX [IX_AspNetUserClaims_UserId] ON [AspNetUserClaims] ([UserId]);

      CREATE INDEX [IX_AspNetUserLogins_UserId] ON [AspNetUserLogins] ([UserId]);

      CREATE INDEX [IX_AspNetUserRoles_RoleId] ON [AspNetUserRoles] ([RoleId]);

      CREATE INDEX [EmailIndex] ON [AspNetUsers] ([NormalizedEmail]);

      CREATE UNIQUE INDEX [UserNameIndex] ON [AspNetUsers] ([NormalizedUserName]) WHERE [NormalizedUserName] IS NOT NULL;

Monday, December 9, 2019

Ikea Lack arcade table based on Retropie

Ikea Lack arcade table based on Retropie. With donated 17 inch monitor the project came in at $179. The most expensive piece was the plexiglass I had cut to size and delivered. Anyway, hope that these images are helpful to anyone who's wanting to try the same project. I'll add explanation later if anyone is interested in this post and wants more info.


























Wednesday, February 8, 2012

Linux Backup Shell Script for Files and MySQL

I assembled this script to backup my web server onto a USB thumbdrive. It mounts the thumbdrive (/dev/sdc1), backs up a couple of directories, does a MySQL dump, and then unmounts the drive. It appends the day of the week to the file name so you can run it daily as a cron job and you’ll get 7-days’ worth of backups on a rotating basis.

#!/bin/sh
####################################
#
# Backup to usb thumbdrive
#
####################################

# What to backup.
backup_files="/home/shared /etc"

# Where to backup to.
dest="/home/administrator/backup/usb"

# Create archive filename.
day=$(date +%A)
hostname=$(hostname -s)
archive_file="$hostname-$day.tgz"
sql_file="$hostname-$day.sql"

# Mount the usb thumbdrive
mount -t vfat /dev/sdc1 /home/administrator/backup/usb -o uid=1000,gid=1000,utf8,dmask=027,fmask=137
echo "Thumbdrive mounted"

# Backup MySQL
echo "Backing up MySQL to $dest/$sql_file"
date
mysqldump --all-databases --password='XXXXXXX' > $dest/$sql_file

# Print start status message.
echo "Backing up $backup_files to $dest/$archive_file"
date

# Backup the files using tar.
tar czf $dest/$archive_file $backup_files

# Print end status message.
echo "Backup finished"
date

# Long listing of files in $dest to check file sizes.
ls -lh $dest

# Unmount the usb thumbdrive
umount /home/administrator/backup/usb
echo "Thumbdrive unmounted"

Thursday, January 5, 2012

Running Node.js Alongside Apache

I decided to set up Node.js on my Linux server running Apache.  Getting a Node.js server running was trivially simple, but I had a bit of trouble getting the mod_rewrite stuff correct to make it pretty (i.e. no port in the URL).  Basically I wanted to create a sub-directory on my site and route all the requests going there to my Node.js server, leaving everything else as-is.  So, http://kevnls.com/ and http://kevnls.com/etc/ would still be handled by Apache, but http://kevnls.com/node/ would be handed-off to Node.js.

Before I began, I already had Node.js serving up content at http://kevnls.com:8000.  I'm not going to address that part, because there are plenty of resources that can help you get to that point.

The first thing I needed to do was create a symlink to the directory where my Node.js server was running (/home/shared/node/).  In my case my CMS has a public/ folder where I can put stuff like 'whatever.txt' and it will resolve to http://kevnls.com/whatever.txt.  So, this is where I created a symlink.  At this point http://kevnls.com/node/ pointed to /home/shared/node/ in the filesystem.

Then in the /home/shared/node/ folder I created my .htaccess file with this mod_rewrite rule:

-----------------------------------------------------------------

Options +FollowSymLinks -Indexes -MultiViews

RewriteEngine on

RewriteRule ^(.*)$ http://kevnls.com:8000/$1 [P]

-----------------------------------------------------------------


Then I needed to get the proper modules running in Apache to handle what I wanted to do.  You can issue the following shell command and just type in the modules you want to enable:


a2enmod

In my case I needed 'proxy', 'proxy_http' and 'rewrite'.



After a quick Apache restart everything was working the way I wanted it.  Now on to the coding.

Sunday, September 25, 2011

Receiving and Processing a SAML 2.0 Response With an HttpServlet Using OpenSAML

Due to the popularity of my prior posts on processing SAML using OpenSAML I put together this code that shows a more real-world-example of how you would setup an HttpServlet to sit and listen for SAML Responses that are posted to it.  Then it just shows what you might do with the Response to get what you need out of it.  Again, I'm just verifying the Signature on the Response, Decrypting the Assertion and then looping through the Attribute nodes to look at the name/value pairs.  There are other things that you might want to do such as receiving the signing-key from the Response itself (if it's being sent), or verifying a Signature on the Assertion if that's what's being signed.  The trouble I've found with SAML implementations is that the spec is consistent, but how people use it isn't, so you'll undoubtedly need to tweak something depending on what your requirements are, but this code should get you started.

import java.io.PrintWriter;
import java.io.IOException;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;

import java.util.List;

import java.security.KeyFactory;
import java.security.KeyStore;
import java.security.PublicKey;
import java.security.cert.CertificateFactory;
import java.security.cert.X509Certificate;
import java.security.interfaces.RSAPrivateKey;
import java.security.spec.X509EncodedKeySpec;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.opensaml.common.binding.BasicSAMLMessageContext;
import org.opensaml.saml2.binding.decoding.HTTPPostDecoder;
import org.opensaml.ws.message.MessageContext;
import org.opensaml.ws.transport.http.HttpServletRequestAdapter;
import org.opensaml.saml2.core.Assertion;
import org.opensaml.saml2.core.Attribute;
import org.opensaml.saml2.core.AttributeStatement;
import org.opensaml.saml2.core.Response;
import org.opensaml.saml2.encryption.Decrypter;
import org.opensaml.xml.XMLObject;
import org.opensaml.xml.encryption.DecryptionException;
import org.opensaml.xml.encryption.InlineEncryptedKeyResolver;
import org.opensaml.xml.security.keyinfo.StaticKeyInfoCredentialResolver;
import org.opensaml.xml.security.x509.BasicX509Credential;
import org.opensaml.xml.signature.Signature;
import org.opensaml.xml.signature.SignatureValidator;
import org.opensaml.xml.validation.ValidationException;

/**
 * @author kevnls
 * If you use this code as a base for your implementation please leave this comment intact.
 * You should add your own name in addition.
 */

public class ProcessSAML extends HttpServlet {
  
    /**
     * Processes requests for both HTTP GET and POST methods.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */

    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {

        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();

        File signatureVerificationPublicKeyFile = new File("C:\\Documents and Settings\\kevnls\\My Documents\\NetBeansProjects\\SAMLReceiver\\files\\IdPSigningCert.cer");
        File decryptionPrivateKeyFile = new File("C:\\Documents and Settings\\kevnls\\My Documents\\NetBeansProjects\\SAMLReceiver\\files\\SPEncryptionCert.jks");
        String decryptionPrivateKeyName = "pvktmp:bd5ba0e0-9718-48ea-b6e6-32cd9c852d76";
        String decryptionPrivateKeyPassword = "!c3c0ld";

        try
        {
            //bootstrap the opensaml stuff
            org.opensaml.DefaultBootstrap.bootstrap();

            // get the message context
            MessageContext messageContext = new BasicSAMLMessageContext();
            messageContext.setInboundMessageTransport(new HttpServletRequestAdapter(request));
            HTTPPostDecoder samlMessageDecoder = new HTTPPostDecoder();
            samlMessageDecoder.decode(messageContext);

            // get the SAML Response
            Response samlResponse = (Response)messageContext.getInboundMessage();

            //get the certificate from the file
            InputStream inputStream2 = new FileInputStream(signatureVerificationPublicKeyFile);
            CertificateFactory certificateFactory = CertificateFactory.getInstance("X.509");
            X509Certificate certificate = (X509Certificate)certificateFactory.generateCertificate(inputStream2);
            inputStream2.close();

            //pull out the public key part of the certificate into a KeySpec
            X509EncodedKeySpec publicKeySpec = new X509EncodedKeySpec(certificate.getPublicKey().getEncoded());

            //get KeyFactory object that creates key objects, specifying RSA
            KeyFactory keyFactory = KeyFactory.getInstance("RSA");

            //generate public key to validate signatures
            PublicKey publicKey = keyFactory.generatePublic(publicKeySpec);

            //create credentials
            BasicX509Credential publicCredential = new BasicX509Credential();

            //add public key value
            publicCredential.setPublicKey(publicKey);

            //create SignatureValidator
            SignatureValidator signatureValidator = new SignatureValidator(publicCredential);

            //get the signature to validate from the response object
            Signature signature = samlResponse.getSignature();

            //try to validate
            try
            {
                signatureValidator.validate(signature);
            }
            catch (ValidationException ve)
            {
                out.println("Signature is not valid.");
                out.println(ve.getMessage());
                return;
            }

            //no validation exception was thrown
            out.println("Signature is valid.");

            //start decryption of assertion

            //load up a KeyStore
            KeyStore keyStore = KeyStore.getInstance("JKS");
            keyStore.load(new FileInputStream(decryptionPrivateKeyFile), decryptionPrivateKeyPassword.toCharArray());

            RSAPrivateKey privateKey = (RSAPrivateKey) keyStore.getKey(decryptionPrivateKeyName, decryptionPrivateKeyPassword.toCharArray());

            //create the credential
            BasicX509Credential decryptionCredential = new BasicX509Credential();
            decryptionCredential.setPrivateKey(privateKey);

            StaticKeyInfoCredentialResolver skicr = new StaticKeyInfoCredentialResolver(decryptionCredential);

            //create a decrypter
            Decrypter decrypter = new Decrypter(null, skicr, new InlineEncryptedKeyResolver());

            //decrypt the first (and only) assertion
            Assertion decryptedAssertion;

            try
            {
                decryptedAssertion = decrypter.decrypt(samlResponse.getEncryptedAssertions().get(0));
            }
            catch (DecryptionException de)
            {
                out.println("Assertion decryption failed.");
                out.println(de.getMessage());
                return;
            }

            out.println("Assertion decryption succeeded.");

            //loop through the nodes to get the Attributes
            //this is where you would do something with these elements
            //to tie this user with your environment
            List attributeStatements = decryptedAssertion.getAttributeStatements();
            for (int i = 0; i < attributeStatements.size(); i++)
            {
                List attributes = attributeStatements.get(i).getAttributes();
                for (int x = 0; x < attributes.size(); x++)
                {
                    String strAttributeName = attributes.get(x).getDOM().getAttribute("Name");

                    List attributeValues = attributes.get(x).getAttributeValues();
                    for (int y = 0; y < attributeValues.size(); y++)
                    {
                        String strAttributeValue = attributeValues.get(y).getDOM().getTextContent();
                        out.println(strAttributeName + ": " + strAttributeValue + " ");
                    }
                }
            }
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
        }

    }

    //
    /**
     * Handles the HTTP GET method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Handles the HTTP POST method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Returns a short description of the servlet.
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "This servlet processes a SAML 2.0 Response.  It verifies the signature, " +
                "decrypts an assertion, and parses out the data in the attribute statements.  " +
                "If you use this code as a base for your implementation please leave the @author comment intact.  " +
                "You should add your own name in addition.";
    }//


}



Anyway, hope this is useful to someone.