How to use makecert.exe to create a self-signed test certificate that can be used with IIS for SSL

February 29, 2016 Leave a comment

Problem: Special options must be specified with makecert.exe, to create a self-signed certificate that can be used with IIS (Microsoft Internet Information Server).

Note: Microsoft recommends to install and use the “Certificate Server” to generate an SSL test certificate (Q216907), instead of using makecert.exe. But using makecert is simpler.

Solution:

The following command can be used to create and import a self-signed SSL test certificate:

makecert -r -pe -n “CN=www.yourserver.com” -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange
-sp “Microsoft RSA SChannel Cryptographic Provider” -sy 12

To install this certificate in IIS 5.0, open the IIS “Web Site Properties”, “Directory Security”, “Server Certificate…”, “Assign an existing certificate” and select the new certificate from the list.

Note: Older versions of makecert.exe do not support the “-pe” option, which makes the private key exportable. If you have an old version of makecert.exe, you can omit the “-pe” option, but then the certificate cannot be exported including the private key.

(The October 2002 version of the Platform SDK (build 3718.1) contains a new version of makecert.exe (5.131) that supports the “-pe” option. The .NET Framework SDK 1.0 of 2002-03-19 contains an old version of makecert.exe that does not support the “-pe” option).

If the private key is exportable, you can export the certificate together with the private key into a PFX (PKCS #12) file as described in Q232136.

Note: SSL server certificates for IIS are stored in the “Personal” (“My”) certificate store of the “computer account” (“localMachine”). The “Certificates” snap-in of the Microsoft Management Console (mmc.exe) must be used to manage these certificates. The normal certificate management window (accessible via “Internet Properties” / “Content” / “Certificates” or via “Control Panel” / “Users and Passwords” / “Advanced” / “Certificates”) cannot be used.

Note: To create a key with more than 512 bits, use the “-len” parameter of makecert.exe.

Author: Christian d’Heureuse (chdh@inventec.ch, www.source-code.biz)

Example: X509.MakeCert

[VB6 equivalent: X509_MakeCert]

Dim nRet As Integer
Dim strNewCertFile As String
Dim strIssuerCert As String
Dim strSubjectPubKeyFile As String
Dim strIssuerPriKeyFile As String
Dim strPassword As String
Dim nCertNum As Integer
Dim nYearsValid As Integer
Dim strDistName As String
Dim strEmail As String
strNewCertFile = “myuser.cer”
strIssuerCert = “myca.cer”
strSubjectPubKeyFile = “mykey.pub”
strIssuerPriKeyFile = “myca.epk”
strPassword = “password” ‘!!
nCertNum = &H101
nYearsValid = 4
strDistName = “CN=My User,O=Test Org,OU=Unit,C=AU,L=My Town,S=State,E=myuser@testorg.com”
strEmail = “myuser@testorg.com”

nRet = X509.MakeCert(strNewCertFile, strIssuerCert, strSubjectPubKeyFile, strIssuerPriKeyFile, _
nCertNum, nYearsValid, strDistName, strEmail, 0, strPassword, 0)
If nRet <> 0 Then
Console.WriteLine(nRet & ” ” & General.LastError())
Else
Console.WriteLine(“Success, created X.509 cert ” & strNewCertFile)
End If

See Also:
X509.MakeCert Method

[Contents] [Index]

Categories: SSL

how do i limit the number of rows returned by an oracle query after ordering

Source http://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering

I did some performance testing between:

A.) Asktom

select * from (
  select a.*, ROWNUM rnum from (
    <select statemenet with order by clause>
  ) a where rownum <= MAX_ROW
) where rnum >= MIN_ROW

B.) Analytic approach

select * from (
  <select statemenet with order by clause>
) where myrow between MIN_ROW and MAX_ROW

C.) Short alternative

select * from (
  select statement, rownum as RN with order by clause
) where a.rn >= MIN_ROW and a.rn <= MAX_ROW

Results:

Table had 10 million records, sort was on an unindexed datetime row:

  • Explain plan showed same value for all three selects (323168)
  • But the winner is AskTom (with analytic following close behind)

Selecting first 10 rows took:

  • AskTom: 28-30 seconds
  • Analytical: 33-37 seconds
  • Short alternative: 110-140 seconds

Selecting rows betwwen 100.000 and 100.010:

  • AskTom: 60 seconds
  • Analytical: 100 seconds

Selecting rows between 9.000.000 and 9.000.010:

  • AskTom: 130 seconds
  • Analytical: 150 seconds
Categories: Oracle Tags:

Getting the space left on the DEFAULT_TABLESPACE of the logged user

November 20, 2014 Leave a comment
SELECT
  ts.tablespace_name,
  TO_CHAR(SUM(NVL(fs.bytes,0))/1024/1024, '99,999,990.99') AS MB_FREE
FROM
  user_free_space fs,
  user_tablespaces ts,
  user_users us
WHERE
  fs.tablespace_name(+)   = ts.tablespace_name
AND ts.tablespace_name(+) = us.default_tablespace
GROUP BY
  ts.tablespace_name;
Categories: Uncategorized Tags:

CREATE USER WITH TABLESPACE in the ORACLE

The syntax for the CREATE USER statement in Oracle/PLSQL is:

CREATE USER user_name 
  IDENTIFIED { BY password
             | EXTERNALLY [ AS 'certificate_DN' ]
             | GLOBALLY [ AS '[ directory_DN ]' ]
             }
  [ DEFAULT TABLESPACE tablespace
  | TEMPORARY TABLESPACE
       { tablespace | tablespace_group }
  | QUOTA integer [ K | M | G | T | P | E ]
        | UNLIMITED }
        ON tablespace
    [ QUOTA integer [ K | M | G | T | P | E ]
        | UNLIMITED }
            ON tablespace
    ]
  | PROFILE profile_name
  | PASSWORD EXPIRE
  | ACCOUNT { LOCK | UNLOCK }
     [ DEFAULT TABLESPACE tablespace
     | TEMPORARY TABLESPACE
         { tablespace | tablespace_group }
     | QUOTA integer [ K | M | G | T | P | E ]
           | UNLIMITED }
           ON tablespace
       [ QUOTA integer [ K | M | G | T | P | E ]
           | UNLIMITED }
           ON tablespace
        ]
     | PROFILE profile
     | PASSWORD EXPIRE
     | ACCOUNT { LOCK | UNLOCK } ]
     ] ;


PARAMETERS OR ARGUMENTS

user_name is the name of the database account that you wish to create.

PROFILE profile_name is optional. It is the name of the profile that you wish to assign to the user account to limit the amount of database resources assigned to the user account. If you omit this option, the DEFAULT profile is assigned to the user.

PASSWORD EXPIRE is optional. If this option is set, then the password must be reset before the user can log into the Oracle database.

ACCOUNT LOCK or UNLOCK is optional. ACCOUNT LOCK disables access to the user account. ACCOUNT UNLOCK enables access to the user account.

 

EXAMPLE

CREATE USER smithj
  IDENTIFIED BY pwd4smithj
  DEFAULT TABLESPACE tbs_perm_01
  TEMPORARY TABLESPACE tbs_temp_01
  QUOTA 20M on tbs_perm_01;

This CREATE USER statement would create a new user called smithj in the Oracle database whose password is pwd4smithj, the default tablespace would be tbs_perm_01 with a quota of 20MB, and the temporary tablespace would be tbs_temp_01.

Categories: Oracle

Delete File C#

February 14, 2014 Leave a comment
string sourceDir = @"c:\current";
string backupDir = @"c:\archives\2008";

try
{
    string[] picList = Directory.GetFiles(sourceDir, "*.jpg");
    string[] txtList = Directory.GetFiles(sourceDir, "*.txt");

    // Copy picture files. 
    foreach (string f in picList)
    {
        // Remove path from the file name. 
        string fName = f.Substring(sourceDir.Length + 1);

        // Use the Path.Combine method to safely append the file name to the path. 
        // Will overwrite if the destination file already exists.
        File.Copy(Path.Combine(sourceDir, fName), Path.Combine(backupDir, fName), true);
    }

    // Copy text files. 
    foreach (string f in txtList)
    {

        // Remove path from the file name. 
        string fName = f.Substring(sourceDir.Length + 1);

        try
        {
            // Will not overwrite if the destination file already exists.
            File.Copy(Path.Combine(sourceDir, fName), Path.Combine(backupDir, fName));
        }

        // Catch exception if the file was already copied. 
        catch (IOException copyError)
        {
            Console.WriteLine(copyError.Message);
        }
    }

    // Delete source files that were copied. 
    foreach (string f in txtList)
    {
        File.Delete(f);
    }
    foreach (string f in picList)
    {
        File.Delete(f);
    }
}

catch (DirectoryNotFoundException dirNotFound)
{
    Console.WriteLine(dirNotFound.Message);
}


source : http://msdn.microsoft.com/en-us/library/system.io.file.delete(v=vs.110).aspx
Categories: C#

Encrypt & Decrypt File C#

February 14, 2014 Leave a comment
using System;
using System.IO;
using System.Security;
using System.Security.Cryptography;
using System.Runtime.InteropServices;
using System.Text;

namespace CSEncryptDecrypt
{
   class Class1
   {
      //  Call this function to remove the key from memory after use for security
      [System.Runtime.InteropServices.DllImport("KERNEL32.DLL", EntryPoint="RtlZeroMemory")]
      public static extern bool ZeroMemory(IntPtr Destination, int Length);
		
      // Function to Generate a 64 bits Key.
      static string GenerateKey() 
      {
         // Create an instance of Symetric Algorithm. Key and IV is generated automatically.
         DESCryptoServiceProvider desCrypto =(DESCryptoServiceProvider)DESCryptoServiceProvider.Create();

         // Use the Automatically generated key for Encryption. 
         return ASCIIEncoding.ASCII.GetString(desCrypto.Key);
      }

      static void EncryptFile(string sInputFilename,
         string sOutputFilename, 
         string sKey) 
      {
         FileStream fsInput = new FileStream(sInputFilename, 
            FileMode.Open, 
            FileAccess.Read);

         FileStream fsEncrypted = new FileStream(sOutputFilename, 
            FileMode.Create, 
            FileAccess.Write);
         DESCryptoServiceProvider DES = new DESCryptoServiceProvider();
         DES.Key = ASCIIEncoding.ASCII.GetBytes(sKey);
         DES.IV = ASCIIEncoding.ASCII.GetBytes(sKey);
         ICryptoTransform desencrypt = DES.CreateEncryptor();
         CryptoStream cryptostream = new CryptoStream(fsEncrypted, 
            desencrypt, 
            CryptoStreamMode.Write); 

         byte[] bytearrayinput = new byte[fsInput.Length];
         fsInput.Read(bytearrayinput, 0, bytearrayinput.Length);
         cryptostream.Write(bytearrayinput, 0, bytearrayinput.Length);
         cryptostream.Close();
         fsInput.Close();
         fsEncrypted.Close();
      }

      static void DecryptFile(string sInputFilename, 
         string sOutputFilename,
         string sKey)
      {
         DESCryptoServiceProvider DES = new DESCryptoServiceProvider();
         //A 64 bit key and IV is required for this provider.
         //Set secret key For DES algorithm.
         DES.Key = ASCIIEncoding.ASCII.GetBytes(sKey);
         //Set initialization vector.
         DES.IV = ASCIIEncoding.ASCII.GetBytes(sKey);

         //Create a file stream to read the encrypted file back.
         FileStream fsread = new FileStream(sInputFilename, 
            FileMode.Open, 
            FileAccess.Read);
         //Create a DES decryptor from the DES instance.
         ICryptoTransform desdecrypt = DES.CreateDecryptor();
         //Create crypto stream set to read and do a 
         //DES decryption transform on incoming bytes.
         CryptoStream cryptostreamDecr = new CryptoStream(fsread, 
            desdecrypt,
            CryptoStreamMode.Read);
         //Print the contents of the decrypted file.
         StreamWriter fsDecrypted = new StreamWriter(sOutputFilename);
         fsDecrypted.Write(new StreamReader(cryptostreamDecr).ReadToEnd());
         fsDecrypted.Flush();
         fsDecrypted.Close();
      } 

      static void Main()
      {
         // Must be 64 bits, 8 bytes.
         // Distribute this key to the user who will decrypt this file.
         string sSecretKey;
         
         // Get the Key for the file to Encrypt.
         sSecretKey = GenerateKey();

         // For additional security Pin the key.
         GCHandle gch = GCHandle.Alloc( sSecretKey,GCHandleType.Pinned );
         
         // Encrypt the file.        
         EncryptFile(@"C:\MyData.txt", 
            @"C:\Encrypted.txt", 
            sSecretKey);

         // Decrypt the file.
         DecryptFile(@"C:\Encrypted.txt", 
            @"C:\Decrypted.txt", 
            sSecretKey);

         // Remove the Key from memory. 
         ZeroMemory(gch.AddrOfPinnedObject(), sSecretKey.Length * 2);
         gch.Free();
      }
   }
}

source : http://support.microsoft.com/kb/307010
Categories: C# Tags:

C# Saving any File to database SQL Server

Since it’s not mentioned what database you mean I’m assuming SQL Server. Below solution works for both 2005 and 2008.

You have to create table with VARBINARY(MAX) as one of the columns. In my example I’ve created Table Raporty with column RaportPlik being VARBINARY(MAX) column.

Method to put file into database from drive:

public static void databaseFilePut(string varFilePath) {
    byte[] file;
    using (var stream = new FileStream(varFilePath, FileMode.Open, FileAccess.Read)) {
        using (var reader = new BinaryReader(stream)) {
            file = reader.ReadBytes((int) stream.Length);       
        }          
    }
    using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetails))
    using (var sqlWrite = new SqlCommand("INSERT INTO Raporty (RaportPlik) Values(@File)", varConnection)) {
        sqlWrite.Parameters.Add("@File", SqlDbType.VarBinary, file.Length).Value = file;
        sqlWrite.ExecuteNonQuery();
    }
}

This method is to get file from database and save it on drive:

public static void databaseFileRead(string varID, string varPathToNewLocation) {
    using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetails))
    using (var sqlQuery = new SqlCommand(@"SELECT [RaportPlik] FROM [dbo].[Raporty] WHERE [RaportID] = @varID", varConnection)) {
        sqlQuery.Parameters.AddWithValue("@varID", varID);
        using (var sqlQueryResult = sqlQuery.ExecuteReader())
            if (sqlQueryResult != null) {
                sqlQueryResult.Read();
                var blob = new Byte[(sqlQueryResult.GetBytes(0, 0, null, 0, int.MaxValue))];
                sqlQueryResult.GetBytes(0, 0, blob, 0, blob.Length);
                using (var fs = new FileStream(varPathToNewLocation, FileMode.Create, FileAccess.Write)) 
                    fs.Write(blob, 0, blob.Length);
            }
    }
}

This method is to get file from database and put it as MemoryStream:

public static MemoryStream databaseFileRead(string varID) {
    MemoryStream memoryStream = new MemoryStream();
    using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetails))
    using (var sqlQuery = new SqlCommand(@"SELECT [RaportPlik] FROM [dbo].[Raporty] WHERE [RaportID] = @varID", varConnection)) {
        sqlQuery.Parameters.AddWithValue("@varID", varID);
        using (var sqlQueryResult = sqlQuery.ExecuteReader())
            if (sqlQueryResult != null) {
                sqlQueryResult.Read();
                var blob = new Byte[(sqlQueryResult.GetBytes(0, 0, null, 0, int.MaxValue))];
                sqlQueryResult.GetBytes(0, 0, blob, 0, blob.Length);
                //using (var fs = new MemoryStream(memoryStream, FileMode.Create, FileAccess.Write)) {
                memoryStream.Write(blob, 0, blob.Length);
                //}
            }
    }
    return memoryStream;
}

This method is to put MemoryStream into database:

public static int databaseFilePut(MemoryStream fileToPut) {
        int varID = 0;
        byte[] file = fileToPut.ToArray();
        const string preparedCommand = @"
                    INSERT INTO [dbo].[Raporty]
                               ([RaportPlik])
                         VALUES
                               (@File)
                        SELECT [RaportID] FROM [dbo].[Raporty]
            WHERE [RaportID] = SCOPE_IDENTITY()
                    ";
        using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetails))
        using (var sqlWrite = new SqlCommand(preparedCommand, varConnection)) {
            sqlWrite.Parameters.Add("@File", SqlDbType.VarBinary, file.Length).Value = file;

            using (var sqlWriteQuery = sqlWrite.ExecuteReader())
                while (sqlWriteQuery != null && sqlWriteQuery.Read()) {
                    varID = sqlWriteQuery["RaportID"] is int ? (int) sqlWriteQuery["RaportID"] : 0;
                }
        }
        return varID;
    }


source : http://stackoverflow.com/questions/2579373/saving-any-file-to-in-the-database-just-convert-it-to-a-byte-array
Categories: C#, SQL Server Tags: ,