Thursday, 9 August 2018

MS-SQL Auto Increment Identity Column Value Jump 1000

Long time ago I noticed a bug in one of my Databases, which I discovered later it was in relation with one of Microsoft's features :) :) :). That bug/feature was not stable and occurs in lower rates "lower than 1% of the time". In my case it happened three times along 8 months. Recently, I caught it again on one of my servers.

The bug is all about the Identity field of any table. Where ID sequence misses its order in an unexpected way. So, instead of counting (1, 2, 3, 4 .. etc) it counts (1, 2, 1001, 1002, 1003, .. etc) or (1, 2, 10001, 10002, 10003, .. etc). Sometimes, SQL jumps the sequence of normal counting in the ID field by 1000 or 10000 steps at once.

That makes too much trouble for my Database. Every time occurs, I manually fix the difference and returns sequence to its normality. But, today, and after some investigation I found the reason and the solution.

The Reason: 
Unexpected SQL-Server restarting causes the cache-technique done for the identity to be mis-confused and then SQL rests it to some new steps ahead, in case of "int" it steps 1000 ahead and in case of "bigint" it steps 10000 ahead.

The solution:
I found four solutions. You can select one of them according to your case:
1- Use [SEQUENCE] object: click here
Which means too much change in your application-code and architecture of the data-provider-layer if you have any.
2- Change server trace flag [-t272]: click here
It is a server wide option that is used to disable any cache of any identity-field in any table of any database in the server. That means other databases may be affected if you have multiple.
3- Use [IDENTITY_CAHCE]: click here
It is Only applied for "SQL-2017 (14.x)", and it is used to disable caching technique for specific databases only.
4- Use [DBCC CHECKIDENT]: click here
Create a stored procedure in master database and add it to the starting chain using [sp_procoption], this master wide [STORED PROCEDURE] should be used to reset [IDENTITY] manually for specific tables in specific database, so, every time SQL-Server is restarted it starts and reset Identity using [DBCC CHECKIDENT].




Monday, 19 March 2018

Teredo, IPV6-tunneling over IPV4-traffic


What Is IPV4?
IPv4 is defined and specified in IETF publication RCF 791. It is used in the packet-switched link layer in the OSI model. IPv4 uses 32-bit addresses for Ethernet communication in five classes, named A, B, C, D and E. Classes A, B and C have a different bit length for addressing the network host. Class D addresses are reserved for multicasting, while class E addresses are reserved for future use. This addressing scheme denotes how many hosts (computers) we can have directly connected to the internet. Class A addresses 127.0.0.0 to 127.255.255.255 cannot be used and is reserved for loopback and diagnostic functions.

Class
From
To
Network/Host ID
Subnet Mask
Networks
Hosts per Network
A
1.0.0.0
126.255.255.255
N.H.H.H
255.0.0.0
126 (27 – 2)
16,777,214 (224 – 2)
B
128.0.0.0
191.255.255.255
N.N.H.H
255.255.0.0
16,382 (214 – 2)
65,534 (216 – 2)
C
192.0.0.0
223.255.255.255
N.N.N.H
255.255.255.0
2,097,150 (221 – 2)
254 (28 – 2)
D
224.0.0.0
239.255.255.255
Reserved for Multicasting
E
240.0.0.0
254.255.255.255
Experimental; used for research

The TCP/IP Version 4 packet is composed of a number of different fields that can be used by the source and intermediary devices to determine the way a specific packet is treated when being transported. 


What Is NAT?
Image resultNetwork Address Translation (NAT) is a method of remapping one IP address space into another by modifying network address information in Internet Protocol (IP) datagram packet headers while they are in transit across a traffic routing device. (NAT) is the process where a network device, usually a firewall, assigns a public address to a computer (or group of computers) inside a private network. The main use of NAT is to limit the number of public IP addresses an organization or company must use, for both economy and security purposes. NAT falls into two categories; Static NAT “i.e. Restricted Cone NAT” and Dynamic NAT “i.e. Port Restricted Cone NAT, Symmetric NAT”. Static NAT is where administrators manually create and maintain the NAT mappings. Dynamic NAT is where the router creates and maintains mappings automatically on demand.


What is Network Interface?
network interface is the point of interconnection between a computer and a private or public network. A network interface is generally a network interface card (NIC), but does not have to have a physical form. Instead, the network interface can be implemented in software.

What Is IPV6?
IPv6 is the Internet's next-generation protocol, designed to replace the current Internet Protocol “i.e. IP-Version-4”. IPv6 allows more users and devices to communicate on the Internet by using bigger numbers to create IP addresses. Under IPv4, every IP address is 32 bits long, which allows 4.3 billion unique addresses. Under IPV6, every IP is 128 bits long, which allows 4.3x1038 unique addresses. It is not a new technology it is here since 90th and is a prominent aspect of the Internet-Of-Things era, believe or not it can provide a 4.3x1028 individual IP-addresses for every human of the 7Billion population on the earth. IPv6 is the future of the Internet Protocol, there is no doubt about that. With only a small handful of people running it today however, it will take a long time until we can effectively 'turn off' IPv4 on our networks. Some people think this may be as long as the year 2025.


To test if your computer “the host” can access IPV6 websites “the web server” you can ping IPV6-address of google “i.e. ping -6 ipv6.google.com”, such like that:

You can trace its route also using “tracert -6 www.google.com”, such like that:


IPv6 on IPv4 Networks
https://msu.edu/~geimerma/images/translation.jpgIPv6 can be routed on the current IPv4 Networks, but special things must be done in order for the IPv4 addressing to work. There are 4 main methodologies to executing this task, which then have specific implementations. The methods are dual-stack, tunneling, translation, and gateway. They each have pros and cons, and each are in use. Dual stack works by using both protocols on each router and computer on the network that does the actual routing and delivery of the traffic. Tunneling encapsulates IPv6 packets, in whole, into IPv4 packets and adding some information to the IPv4 header and sending it. The reciever sees the special info and strips the IPv6 packet out and uses it. Translation is used when an IPv4 only node on the network tries to communicate with an IPv6 only node. There are a few protocols that handle this translation, such as NAT-PT and Bump-in-the-API. Gateway methods are fairly popular (and many times free!) and are easy to use. A gateway computer is set up on some network, which can be your Internet service provider (if you are lucky) or a 3rd-party company such as 6gate or SixXs (links open in new window). See this simple representation of how this works. This picture shows a client computer running IPv6 connecting to The Internet through a gateway that allows IPv4 sites to be reached that normally would not be. This also works in the other direction, IPv4 clients could connect to IPv6 servers and services through the gateway. Also note that not all computers on The Internet run IPv4, and some do indeed run IPv6 and utilize translational gateways such as this. Another important issue with gateways is that they tend to be application specific. That means that most likely the gateway only handle one or a handful of protocols, such as http. This can be a problem if the client wants to use IPv6 only and connect to a service that uses IPv4 and the gateway doesn't support it. At this point the client must use some other means of access.

What Is Teredo?
https://technet.microsoft.com/en-us/library/bb457011.tered02_big(l=en-us).gifTeredo is an IPv6 over IPv4 tunneling mechanism. Its important distinguishing feature when compared to other tunneling methods is that it works even if there are NATs in the IPv4 path. This Microsoft developped tunnelling protocol is defined by RFC4380. Windows has a built in Teredo implementation. There are also implementations of it for at least Linux, FreeBSD and Mac OS X. These include Miredo and nici-teredo. Teredo uses the IPv6 prefix 2001:0::/32 to represent the IPv4 identity of the host. Teredo clients (hosts) connect to a Teredo server when they start up. After that connection is established the client and the server negotiate the address for the client. Public Teredo servers establishes a trusted connection with hosts transmitting bubble-packets. On May 4th, 2015, Microsoft began deactivating its publicly available Teredo servers that were configured as the default servers for Windows Vista and Windows 7 clients, “win10.ipv6.microsoft.com” not work (as per October 2015). Teredo works well using a third party Teredo Server, Ones you picked up the one you want, you may add it as being your teredo server to use. Third party Teredo servers are open to anyone. To configure a Teredo client, use one of the following server addresses:
  • teredo.trex.fi
  • teredo.ipv6.microsoft.com
  • teredo.remlab.net
  • teredo2.remlab.net
  • debian-miredo.progsoc.org
  • teredo.ginzado.ne.jp
  • teredo.iks-jena.de


Use netsh command line
Netsh is a command-line scripting utility that allows you to, either locally or remotely, display or modify the network configuration of a computer that is currently running. Netsh also provides a scripting feature that allows you to run a group of commands in batch mode against a specified computer. You can use the following command line to set a Teredo server “netsh int ipv6 set teredo client teredo.remlab.net”.

  • To change Teredo state: netsh interface Teredo set state type=default
  • To Show Teredo  state: netsh interface Teredo show state
  • To disable Teredo: netsh interface teredo set state disable
  • To set a serever: netsh interface teredo set state servername=terdoServerName

Use OpenDNS!
Domain Name Servers (DNS) are the Internet's equivalent of a phone book. They maintain a directory of domain names and translate them to Internet Protocol (IP) addresses. This is necessary because, although domain names are easy for people to remember, computers or machines, access websites based on IP addresses. OpenDNS now supports IPv6 addresses — meaning that, by using the OpenDNS Sandbox, you’ll be able to resolve your DNS using IPv6 DNS servers. IPv6 supports a far larger number of addresses than IPv4, which is why the change is taking place now — since IPv4 was implemented in 1981, the Internet has grown dramatically, and there are no more available IPv4 addresses. The last block of IPv4 addresses have been allocated, and it’s time to get your network ready.
C:\Users\ALGHOOLW\AppData\Local\Microsoft\Windows\INetCacheContent.Word\i394^cimgpsh_orig.png 
Teredo Port Security!
Windows Firewall with Advanced Security includes a stateful firewall that allows you to determine which network traffic is permitted to pass between your computer and the network. It also includes connection security rules that use Internet Protocol security (IPsec) to protect traffic as it travels across the network. Create a rule that applies to inbound connections, go to the Inbound Rules section. Create a rule that applies to outbound connections, go to the Outbound Rules section. Then, you can use the Actions panel on the right side of the window and click or tap New Rule. Select Port and enable Teredo to map your IPV6 packets through 62675 port.


Solve Windows Home Edition Problems!
  • You will need to manually create a registry key to enable IPV6 transitioning: HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\TCPIP\v6Transition With a value named “Teredo_DefaultQualified” and equals “Enabled”.
  • You will need to manually enable Teredo by modifying the registry value “DisabledComponents” to become “0x0” instead of “0x8e” at the registry key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip6\Parameters


Test your IPv6 connectivity

Visit test-ipv6.com to determine reachability of IPV6 servers. This test is entirely a client-side JavaScript with a series of ajax requests that are made from the web server, using various DNS names that force the use of IPv6 only, or dual stack, or other such scenarios. The pass/fail of such fetches, as well as how long they take, are considered.