Configuring a VPN Gateway

A VPN gateway connection relies on multiple resources that are configured with specific settings. Most of the resources can be configured separately, although some resources must be configured in a certain order.

Reference : Microsoft Docs

Design

It's important to know that there are different configurations available for VPN gateway connections. You need to determine which configuration best fits your needs. For example, Point-to-Site, Site-to-Site, and coexisting ExpressRoute/Site-to-Site connections all have different instructions and configuration requirements. For information about design and to view connection topology diagrams, see Design.

Planning table

The following table can help you decide the best connectivity option for your solution.

refPLANNING TABLE

	Point-to-Site	Site-to-Site	ExpressRoute
Azure Supported Services	Cloud Services and Virtual Machines	Cloud Services and Virtual Machines	Services list
Typical Bandwidths	Based on the gateway SKU	Typically < 1 Gbps aggregate	50 Mbps, 100 Mbps, 200 Mbps, 500 Mbps, 1 Gbps, 2 Gbps, 5 Gbps, 10 Gbps
Protocols Supported	Secure Sockets Tunneling Protocol (SSTP), OpenVPN and IPsec	IPsec	Direct connection over VLANs, NSP's VPN technologies (MPLS, VPLS,...)
Routing	RouteBased (dynamic)	We support PolicyBased (static routing) and RouteBased (dynamic routing VPN)	BGP
Connection resiliency	active-passive	active-passive or active-active	active-active
Typical use case	Secure access to Azure virtual networks for remote users	Dev / test / lab scenarios and small to medium scale production workloads for cloud services and virtual machines	Access to all Azure services (validated list), Enterprise-class and mission critical workloads, Backup, Big Data, Azure as a DR site
SLA	SLA	SLA	SLA
Pricing	Pricing	Pricing	Pricing
Technical Documentation	VPN Gateway Documentation	VPN Gateway Documentation	ExpressRoute Documentation
FAQ	VPN Gateway FAQ	VPN Gateway FAQ	ExpressRoute FAQ

VPN Gateway, Peering in Azure!!

When I was studying about AZ-303 exam. I found a lot of confusion about vnet peering, vnet gateway, subnet gateway, hence thought to pen down below.

What is VPN Gateway?

A VPN gateway is a specific type of virtual network gateway that is used to send encrypted traffic between an Azure virtual network and an on-premises location over the public Internet. 

You can also use a VPN gateway to send encrypted traffic between Azure virtual networks over the Microsoft network. 

Each virtual network can have only one VPN gateway. However, you can create multiple connections to the same VPN gateway. When you create multiple connections to the same VPN gateway, all VPN tunnels share the available gateway bandwidth.

What is a virtual network gateway?

A virtual network gateway is composed of two or more VMs that are deployed to a specific subnet you create called the gateway subnet. 

Virtual network gateway VMs contain routing tables and run specific gateway services. These VMs are created when you create the virtual network gateway. You can't directly configure the VMs that are part of the virtual network gateway.

When you configure a virtual network gateway, you configure a setting that specifies the gateway type. The gateway type determines how the virtual network gateway will be used and the actions that the gateway takes. The gateway type 'Vpn' specifies that the type of virtual network gateway created is a 'VPN gateway'. This distinguishes it from an ExpressRoute gateway, which uses a different gateway type. A virtual network can have two virtual network gateways; one VPN gateway and one ExpressRoute gateway. For more information, see Gateway types.

Creating a gateway can often take 45 minutes or more, depending on the selected gateway SKU. 

When you create a virtual network gateway, gateway VMs are deployed to the gateway subnet and configured with the settings that you specify. 

After you create a VPN gateway, you can create an IPsec/IKE VPN tunnel connection between that VPN gateway and another VPN gateway (VNet-to-VNet),

 or 

create a cross-premises IPsec/IKE VPN tunnel connection between the VPN gateway and an on-premises VPN device (Site-to-Site). 

You can also create a Point-to-Site VPN connection (VPN over OpenVPN, IKEv2, or SSTP), which lets you connect to your virtual network from a remote location, such as from a conference or from home.

VNet Peering vs VPN Gateway

You can also use VPN gateways to establish connectivity between VNets.

Components

• VPN gateway within a special subnet called GatewaySubnet.
• Public IP addressing for each VPN gateway to establish the connection.
• IPSec IKE site-to-site VPN tunnel that ensures all traffic is encrypted.

Which one to use?

• VNet peering: Designed for VNet-to-Vnet connectivity. Typically used for private low-latency interconnectivity.
• VPN gateway: Designed for hybrid connectivity. Generally used where encryption and/or transitive routing is needed.
• VNet Peering and VPN Gateways can also co-exist via gateway transit

Reference : Microsoft  

Find columns across database in SQL Server

Best way to find a column referenced in different tables in database using SQL Server

select t.name from sys.columns c

inner join sys.tables t

on c.object_id = t.object_id

where c.name = '<Column Name>'

Find all tables without Primary Keys

Find all tables without Primary Keys

This is very simple but effective script. It list all the table without primary keys.


SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableNameFROM sys.tablesWHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0ORDER BY SchemaName, TableName;GO


Thats all. Happy Querying :).

Find given text in all stored procedures in SQL Server

Recently, we were working on requirement and need to search a text in list of Stored Procedures. The funny thing, that stored procedure list is more than 2000.  So, I started googling, and find following ways.

1) 

SELECT 
       OBJECT_NAME(object_id), 
       OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%TEXT%'

2)

Select object_name(object_id), definition
From sys.sql_modules

Where definition like '%TEXT%' and objectpropertyex(object_id, 'isProcedure')=1


e.g. Lets take example, you need to search 100 in all stored procedure. Use following query.

1)  SELECT 
       OBJECT_NAME(object_id), 
       OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%100%'


2)

Select object_name(object_id), definition
From sys.sql_modules

Where definition like '%100%' and objectpropertyex(object_id, 'isProcedure')=1

That's it. You will get results matching criteria. Just Enjoy. Happy coding.

Exception : Path is too long - RESOLVED

While working on a problem today, we were getting following exception. I agree that we were using very long path but we can't do anything with folder structure. It was a client share.

Exception:

The specified path, file name, or both are too long. The fully qualified file name must be less than 260 characters, and the directory name must be less than 248 characters.

   at System.IO.Path.SafeSetStackPointerValue(Char* buffer, Int32 index, Char value)

   at System.IO.Path.NormalizePathFast(String path, Boolean fullCheck)

   at System.IO.Path.NormalizePath(String path, Boolean fullCheck)

   at System.IO.Path.GetFullPathInternal(String path)

   at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)

   at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access)

     at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)

   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)

   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()

   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)

   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)

   at System.Threading.ThreadHelper.ThreadStart()

After searching on internet, I came to know that standard .NET API doesn't support that. Hence we moved  back to COM or WinAPI era i.e. full power to play with system. One of the API that suit our requirement is : 

CopyFile

This is how we fixed our issue:

1. Copy respective file using above API to a temporary location.

2. Performed required operations.

3. Delete file from temporary location.

As we have a .NET based application, following is the way to use this API:

Step 1:

Declare P/I invoke statement

 [DllImport("kernel32.dll", CharSet = CharSet.Unicode)]

        static extern bool CopyFile(string lpExistingFileName, string lpNewFileName, bool bFailIfExists);

where 

lpNewFileName - New path including file name.

lpExistingFileName - is a LONG file path including file name.  It can be of two types:

long path is always prefixed with : "\\?\"  when using above API.

a) In case of local drive:  \\?\D:\very long path

b) In case of network drive:

"\\?\" prefix is used with paths constructed according to the universal naming convention (UNC). To specify such a path using UNC, use the "\\?\UNC\" prefix. For example, "\\?\UNC\server\share", where "server" is the name of the computer and "share" is the name of the shared folder. These prefixes are not used as part of the path itself. They indicate that the path should be passed to the system with minimal modification, which means that you cannot use forward slashes to represent path separators, or a period to represent the current directory, or double dots to represent the parent directory. Because you cannot use the "\\?\" prefix with a relative path, relative paths are always limited to a total of MAX_PATH characters.

Example:

\\?\UNC\serverXYZ\Myfoldershare\very long path

Step 2:

Call above API as normal method.

 CopyFile(reallyLongPath, destination, false);

That's it.  File will copy to your destination path. Now, you can play as needed.

Note: 

Many but not all file I/O APIs support "\\?\"; you should look at the reference topic for each API to be sure.

Impersonation in Console/Windows Application in same and cross domain

After Go live of any application, there are issues which occurred for certain users sometimes. To nail down these issues, we need to execute code under that user i.e. impersonate user identities and run application under his user ID.

In Web application, this is quite straightforward and same can be achieved using web.config using location attribute like:

<location path="<Page/Service Path>">

    <system.web>

      <identity impersonate="true" />

    </system.web>

  </location>

<location path="<Page/Service Path>">

    <system.web>

      <identity impersonate="true" userName="<user Name>" password="<Password>"/>

    </system.web>

  </location>

Now, question comes, how to achieve same in console/Windows application if needed. so.. Here you go:

For impersonation in Console/Windows application, best and simple way is to use P/I invoke.  Following sample you can use for same:

Add following method at Class level:

[DllImport("advapi32.dll", SetLastError = true, CharSet = CharSet.Unicode)]

        public static extern bool LogonUser(String lpszUsername, String lpszDomain, String lpszPassword,

            int dwLogonType, int dwLogonProvider, ref IntPtr phToken);  

In your method:

Same Domain

IntPtr tokenHandle = IntPtr.Zero;

            bool returnValue = LogonUser("<User Name>", "<Domain>", "<Password>", 2, 0, ref tokenHandle); 

            WindowsIdentity newId = new WindowsIdentity(tokenHandle);

            using (WindowsImpersonationContext impersonatedUser = newId.Impersonate())

            {

// Add code to be executed under that user

}


Cross Domain:

This is little tricky. Follow given steps: 

1. Create class Impersonation.cs

2. Add following P/I invokes and enums

[DllImport("advapi32.dll", SetLastError = true)]
        private static extern int LogonUser(
        string lpszUserName,
        string lpszDomain,
        string lpszPassword,
        int dwLogonType,
        int dwLogonProvider,
        ref IntPtr phToken);

        [DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)]
        private static extern int DuplicateToken(
        IntPtr hToken,
        int impersonationLevel,
        ref IntPtr hNewToken);

        [DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)]
        private static extern bool RevertToSelf();

        [DllImport("kernel32.dll", CharSet = CharSet.Auto)]
        private static extern bool CloseHandle(
        IntPtr handle);

        private const int LOGON32_LOGON_INTERACTIVE = 2;
        private const int LOGON32_PROVIDER_DEFAULT = 0;

        enum LogonType
        {
            Interactive = 2,
            Network = 3,
            Batch = 4,
            Service = 5,
            Unlock = 7,
            NetworkClearText = 8,
            NewCredentials = 9
        }
        enum LogonProvider
        {
            Default = 0,
            WinNT35 = 1,
            WinNT40 = 2,
            WinNT50 = 3

        }

3. Use following method

        public void ImpersonateUser(

        string userName,

        string domain,

        string password)

        {

            WindowsIdentity tempWindowsIdentity = null;

            IntPtr token = IntPtr.Zero;

            IntPtr tokenDuplicate = IntPtr.Zero;

            try

            {

                if (RevertToSelf())

                {

                    if (LogonUser(

                    userName,

                    domain,

                    password,

                    (int)LogonType.NewCredentials,

                    (int)LogonProvider.WinNT50,

                    //LOGON32_LOGON_INTERACTIVE,

                        //LOGON32_PROVIDER_DEFAULT,

                    ref token) != 0)

                    {

                        if (DuplicateToken(token, 2, ref tokenDuplicate) != 0)

                        {

                            tempWindowsIdentity = new WindowsIdentity(tokenDuplicate);

                            impersonationContext = tempWindowsIdentity.Impersonate();

                        }

                        else

                        {

                            throw new Win32Exception(Marshal.GetLastWin32Error());

                        }

                    }

                    else

                    {

                        throw new Win32Exception(Marshal.GetLastWin32Error());

                    }

                }

                else

                {

                    throw new Win32Exception(Marshal.GetLastWin32Error());

                }

            }

            finally

            {

                if (token != IntPtr.Zero)

                {

                    CloseHandle(token);

                }

                if (tokenDuplicate != IntPtr.Zero)

                {

                    CloseHandle(tokenDuplicate);

                }

            }

        }

4. Create a instance of class as given below and add your code segment 

using (Impersonator imperso = new Impersonator("<userName>","<domain>","Password"))
{
 //Add your code here

}

That's it! ... You are all set to execute your code section under that user.

Thanks!