Hello Vasilii Mikhail

Is there any other way (example NAT rule) that can have the same result? I tried to avoid changing the VPN tunnel. If that is the only way, appreciate if you could guide me on the command involved?

Hello.

As an option (without tunnel interface) you may include your tcp Fa4:3306 into encryption ACL, so HQ would access the service using public IP-address.

PS: please find VTI configuration white paper here - http://www.cisco.com/en/US/technologies/tk583/tk372/technologies_white_paper0900aecd8029d629_ps6635_Products_White_Paper.html 

Hi Vasilli Mikhail

Can you show me the rule for Fa4:3306 into encryption ACL?

Hello.

Please provide your crypto map configuration along with ACLs.

crypto map hotel_map 10 ipsec-isakmp
 set peer 111.111.111.157
 set transform-set myset
 match address acl_vpn

ip access-list extended acl_nat
 deny   ip 10.10.29.0 0.0.0.255 10.10.29.0 0.0.0.255
 permit ip 10.10.29.0 0.0.0.255 any
ip access-list extended acl_vpn
 permit ip 10.10.29.0 0.0.0.255 10.10.29.0 0.0.0.255

ip nat inside source static tcp 10.10.29.10 3306 interface FastEthernet4 3306

At interface fa0/4, I have this
crypto map hotel_map

Hello.

I wonder how VPN ACL could be 10.10.29.0/24 to 10.10.29.0/24, as I thought it had to be 10.10.29.0/24 to 10.10.40.0/24; so, looks like it's not your real configuration (of security concerns?), as a result, I can't spell you the exact configuration.

 ---

Basically, you need to add to your VPN_ACL:

permit tcp <public IP-address> eq 3306 10.10.40.0 0.0.0.255

(and symmetric entry on the other side). 

Also on remote side you need to exclude the traffic 10.10.40.0/24 -> to the server from NAT ACL.

Hello

You are correct. I made a mistake during change IP for security reason. It should be 10.10.40.0. Now my acl look like below. It still not working Anything I need to add?

Hotel:

ip access-list extended acl_nat
 deny   ip 10.10.29.0 0.0.0.255 10.10.40.0 0.0.0.255
 permit ip 10.10.29.0 0.0.0.255 any
ip access-list extended acl_vpn
 permit ip 10.10.29.0 0.0.0.255 10.10.40.0 0.0.0.255
 permit tcp 219.92.49.228 0.0.0.3 eq 3306 10.9.4.0 0.0.0.255

HQ:

ip access-list extended acl_nat
 deny   ip 10.10.40.0 0.0.0.255 10.10.29.0 0.0.0.255
 permit ip 10.10.40.0 0.0.0.255 any
ip access-list extended acl_vpn
 permit ip 10.10.40.0 0.0.0.255 10.10.29.0 0.0.0.255

Finally I change the VPN from crypto to tunnel (follow the documents you link to me) and it work great. Thanks a lot and appreciated your patient and helpful.

Hello

As mentioned earlier, I recreate my tunnel with VTI. The tunnel establish and ping from HQ Server to Hotel Server fine without packet lost. It was on Saturday night. The tunnel work fine for Saturday and Sunday but not on Monday when all staff in HQ start to work.

Packet drop seriously (sometime more than 70%) and the tunnel is unusable. During Monday in certain time period on and off the ping back to normal for 20-30 minutes and drop packet again.

I tried to change MTU at interface tunnel 0 but it make no different. During mid night when no people turn on computer, I can get good ping result regardless I set MTU  and adjust-mss or not.

I suspect packet drop cause by user try to access file from HQ to Hotel via tunnel. During last Saturday after I successfully established tunnel, I have no problem ping from HQ Server to Hotel Server. I also try to login Hotel Database server and it respond. Database login is not possible when people start work on Monday. 

Help is appreciated.

HQ Tunnel:

interface Tunnel0
 ip address 192.168.xxx.2 255.255.255.0
 ip mtu 1400
 ip tcp adjust-mss 1300
 tunnel source 111.111.111.157
 tunnel mode ipsec ipv4
 tunnel destination 222.222.222.230
 tunnel protection ipsec profile VTI
 service-policy output MAP-VPN

Hotel Tunnel:

interface Tunnel0
 ip address 192.168.xxx.1 255.255.255.0
 ip mtu 1400
 ip tcp adjust-mss 1300
 tunnel source 222.222.222.230
 tunnel mode ipsec ipv4
 tunnel destination 111.111.111.157
 tunnel protection ipsec profile VTI
 service-policy output MAP-VPN

Hello.

Please use "ip mtu 1400" + "ip tcp adjust-mss 1360".

During the issue, please collect "show interface Tunnel0" + "show interface <physical WAN interface>" + "show policy-map interface Tu0" +"show proc cpu history" +"show ver" from both devices.

Please attach ping results here when you ping 192.168.xxx.1 from HQ.

HQ show result:

HQ Side
HQ#show run interface tunnel 0
interface Tunnel0
 ip address 192.168.xxx.2 255.255.255.0
 ip mtu 1400
 ip tcp adjust-mss 1360
 keepalive 10 3
 tunnel source 111.111.111.157
 tunnel mode ipsec ipv4
 tunnel destination 222.222.222.230
 tunnel protection ipsec profile VTI
 service-policy output MAP-VPN
end

HQ#show interface Tunnel0
Tunnel0 is up, line protocol is up
  Hardware is Tunnel
  Internet address is 192.168.xxx.2/24
  MTU 17886 bytes, BW 100 Kbit/sec, DLY 50000 usec,
     reliability 255/255, txload 1/255, rxload 1/255
  Encapsulation TUNNEL, loopback not set
  Keepalive set (10 sec), retries 3
  Tunnel source 111.111.111.157, destination 222.222.222.230
  Tunnel protocol/transport IPSEC/IP
  Tunnel TTL 255
  Tunnel transport MTU 1438 bytes
  Tunnel transmit bandwidth 8000 (kbps)
  Tunnel receive bandwidth 8000 (kbps)
  Tunnel protection via IPSec (profile "VTI")
  Last input never, output never, output hang never
  Last clearing of "show interface" counters never
  Input queue: 0/75/0/0 (size/max/drops/flushes); Total output drops: 13
  Queueing strategy: fifo
  Output queue: 0/0 (size/max)
  5 minute input rate 0 bits/sec, 0 packets/sec
  5 minute output rate 0 bits/sec, 0 packets/sec
     1439125 packets input, 515694232 bytes, 0 no buffer
     Received 0 broadcasts, 0 runts, 0 giants, 0 throttles
     0 input errors, 0 CRC, 0 frame, 0 overrun, 0 ignored, 0 abort
     1198939 packets output, 195014932 bytes, 0 underruns
     0 output errors, 0 collisions, 0 interface resets
     0 unknown protocol drops
     0 output buffer failures, 0 output buffers swapped out

HQ#show interface fastEthernet 4
FastEthernet4 is up, line protocol is up
  Hardware is PQII_PRO_UEC, address is XXXX.xxxx.xxxx (bia xxxx.xxxx.xxxx)
  MTU 1500 bytes, BW 100000 Kbit/sec, DLY 100 usec,
     reliability 255/255, txload 1/255, rxload 7/255
  Encapsulation 802.1Q Virtual LAN, Vlan ID  1., loopback not set
  Keepalive set (10 sec)
  Full-duplex, 100Mb/s, 100BaseTX/FX
  ARP type: ARPA, ARP Timeout 04:00:00
  Last input 5w0d, output 00:00:00, output hang never
  Last clearing of "show interface" counters never
  Input queue: 0/75/0/0 (size/max/drops/flushes); Total output drops: 0
  Queueing strategy: fifo
  Output queue: 0/40 (size/max)
  5 minute input rate 3000000 bits/sec, 450 packets/sec
  5 minute output rate 186000 bits/sec, 195 packets/sec
     1934464614 packets input, 2014433383 bytes
     Received 0 broadcasts, 0 runts, 0 giants, 0 throttles
     57 input errors, 0 CRC, 0 frame, 0 overrun, 57 ignored
     0 watchdog
     0 input packets with dribble condition detected
     800346174 packets output, 465540523 bytes, 0 underruns
     0 output errors, 0 collisions, 4 interface resets
     0 unknown protocol drops
     0 babbles, 0 late collision, 0 deferred
     0 lost carrier, 0 no carrier
     0 output buffer failures, 0 output buffers swapped out

HQ#show policy-map interface Tu0
 Tunnel0
  Service-policy output: MAP-VPN

    Class-map: class-default (match-any)
      1198343 packets, 138675332 bytes
      5 minute offered rate 15000 bps, drop rate 0 bps
      Match: any
      Queueing
      queue limit 64 packets
      (queue depth/total drops/no-buffer drops) 0/0/0
      (pkts output/bytes output) 0/0
      shape (average) cir 128000, bc 512, be 512
      target shape rate 128000

HQ#show proc cpu history
HQ   07:22:02 AM Tuesday Apr 28 2015 UTC
    111111111111111111111111111111111     11111111111111111111
    000111118888800000444443333344444999991111111111000001111166
100
 90
 80
 70
 60
 50
 40
 30
 20         *****
 10 ************************************************************
   0....5....1....1....2....2....3....3....4....4....5....5....6
             0    5    0    5    0    5    0    5    0    5    0
               CPU% per second (last 60 seconds)

    11112222221221111121 1 2111111111  12121111111  111  11   11
    411069332084061044485999000786060893010733056899772941644722
100
 90
 80
 70
 60
 50
 40
 30     **                 *
 20     ##*##**##*    #* * *   *** *    * **   ***  **    *
 10 ####################*#############*###########**###* *#  *##
   0....5....1....1....2....2....3....3....4....4....5....5....6
             0    5    0    5    0    5    0    5    0    5    0
               CPU% per minute (last 60 minutes)
              * = maximum CPU%   # = average CPU%

    2322232 11     1211 22221224222 1        1             111   4  112  1 1
    007883143184547144150848736760140845338681989646647679900146408715457872
100
 90
 80
 70
 60
 50                            *
 40                            *                                 *
 30  *****               * *  ***                                *
 20 *******         *   ***********                              *   **  *
 10 ##*###* *** * *******#########* ** *  ******** ** ******** * ********#**
   0....5....1....1....2....2....3....3....4....4....5....5....6....6....7..
             0    5    0    5    0    5    0    5    0    5    0    5    0
                   CPU% per hour (last 72 hours)
                  * = maximum CPU%   # = average CPU%

HQ# show ver
Cisco IOS Software, C880 Software (C880DATA-UNIVERSALK9-M), Version 15.0(1)M5, RELEASE SOFTWARE (fc2)
Technical Support: http://www.cisco.com/techsupport
Copyright (c) 1986-2011 by Cisco Systems, Inc.
Compiled Wed 23-Feb-11 19:52 by prod_rel_team

ROM: System Bootstrap, Version 12.4(22r)YB5, RELEASE SOFTWARE (fc1)

HQ uptime is 21 weeks, 1 day, 5 hours, 32 minutes
System returned to ROM by power-on
System image file is "flash:c880data-universalk9-mz.150-1.M5.bin"
Last reload type: Normal Reload

This product contains cryptographic features and is subject to United
States and local country laws governing import, export, transfer and
use. Delivery of Cisco cryptographic products does not imply
third-party authority to import, export, distribute or use encryption.
Importers, exporters, distributors and users are responsible for
compliance with U.S. and local country laws. By using this product you
agree to comply with applicable laws and regulations. If you are unable
to comply with U.S. and local laws, return this product immediately.

A summary of U.S. laws governing Cisco cryptographic products may be found at:
http://www.cisco.com/wwl/export/crypto/tool/stqrg.html

If you require further assistance please contact us by sending email to
export@cisco.com.

Cisco 881 (MPC8300) processor (revision 1.0) with 236544K/25600K bytes of memory.
Processor board ID FGL1518214F

5 FastEthernet interfaces
1 Virtual Private Network (VPN) Module
256K bytes of non-volatile configuration memory.
125440K bytes of ATA CompactFlash (Read/Write)

License Info:

License UDI:

-------------------------------------------------
Device#   PID                   SN
-------------------------------------------------
*0        CISCO881-K9           FGL1518214F

License Information for 'c880-data'
    License Level: advsecurity   Type: Permanent
    Next reboot license Level: advsecurity

Configuration register is 0x2102

HQ#

Thank you for your valuable information. I am so stupid to follow all the setting from the link included traffic shaping 128000 in the policy. I removed that and the magic appear. Everything run smooth.

Also thank you for your patient :)