Error with Azure SQL Server

Hi, I run a ejabberd node on an Azure Virtual Machine and I use odbc for most of the funcionalities.

Problem is, after some days, ejabberd loses the connection to the database and it seems it cannot restore it correctly, so I have to restart ejabberd itself manually.

Error:

2016-12-28 16:43:40.084 [error] <0.24869.2>@ejabberd_sql:check_error:1033 SQL query 'Q9525209' at {sql_queries,145} failed: <<"[FreeTDS][SQL Server]Communication link failure SQLSTATE$

I post the configuration down here, hope for some help :)

###
###'           ejabberd configuration file
###
###

### The parameters used in this configuration file are explained in more detail
### in the ejabberd Installation and Operation Guide.
### Please consult the Guide in case of doubts, it is included with
### your copy of ejabberd, and is also available online at
### http://www.process-one.net/en/ejabberd/docs/

### The configuration file is written in YAML.
### Refer to http://en.wikipedia.org/wiki/YAML for the brief description.
### However, ejabberd treats different literals as different types:
###
### - unquoted or single-quoted strings. They are called "atoms".
###   Example: dog, 'Jupiter', '3.14159', YELLOW
###
### - numeric literals. Example: 3, -45.0, .0
###
### - quoted or folded strings.
###   Examples of quoted string: "Lizzard", "orange".
###   Example of folded string:
###   > Art thou not Romeo,
###     and a Montague?

###.  =======
###'  LOGGING

##
## loglevel: Verbosity of log files generated by ejabberd.
## 0: No ejabberd log at all (not recommended)
## 1: Critical
## 2: Error
## 3: Warning
## 4: Info
## 5: Debug
##
loglevel: 4

##
## rotation: Describe how to rotate logs. Either size and/or date can trigger
## log rotation. Setting count to N keeps N rotated logs. Setting count to 0
## does not disable rotation, it instead rotates the file and keeps no previous
## versions around. Setting size to X rotate log when it reaches X bytes.
## To disable rotation set the size to 0 and the date to ""
## Date syntax is taken from the syntax newsyslog uses in newsyslog.conf.
## Some examples:
##  $D0     rotate every night at midnight
##  $D23    rotate every day at 23:00 hr
##  $W0D23  rotate every week on Sunday at 23:00 hr
##  $W5D16  rotate every week on Friday at 16:00 hr
##  $M1D0   rotate on the first day of every month at midnight
##  $M5D6   rotate on every 5th day of the month at 6:00 hr
##
log_rotate_size: 10485760
log_rotate_date: ""
log_rotate_count: 1

##
## overload protection: If you want to limit the number of messages per second
## allowed from error_logger, which is a good idea if you want to avoid a flood
## of messages when system is overloaded, you can set a limit.
## 100 is ejabberd's default.
log_rate_limit: 100

##
## watchdog_admins: Only useful for developers: if an ejabberd process
## consumes a lot of memory, send live notifications to these XMPP
## accounts.
##
## watchdog_admins:
##   - "admin@anuserRemoteVM.xny2e1cwaibufevbp43v3ynqjf.ax.internal.cloudapp.net"

###.  ===============
###'  NODE PARAMETERS

##
## net_ticktime: Specifies net_kernel tick time in seconds. This options must have
## identical value on all nodes, and in most cases shouldn't be changed at all from
## default value.
##
## net_ticktime: 60

###.  ================
###'  SERVED HOSTNAMES

##
## hosts: Domains served by ejabberd.
## You can define one or several, for example:
## hosts:
##   - "example.net"
##   - "example.com"
##   - "example.org"
##

## - "anuserRemoteVM.xny2e1cwaibufevbp43v3ynqjf.ax.internal.cloudapp.net"
hosts:
  - "SOME.IP.NUMBER.A"

##
## route_subdomains: Delegate subdomains to other XMPP servers.
## For example, if this ejabberd serves example.org and you want
## to allow communication with an XMPP server called im.example.org.
##
## route_subdomains: s2s

###.  ===============
###'  LISTENING PORTS

##
## listen: The ports ejabberd will listen on, which service each is handled
## by and what options to start it with.
##
listen:
  -
    port: 5222
    module: ejabberd_c2s
    certfile: "/opt/ejabberd-16.06/conf/server.pem"
    starttls: true
    ## To enforce TLS encryption for client connections,
    ## use this instead of the "starttls" option:
    ## starttls_required: true
    ##
    ## Custom OpenSSL options
    ##
    protocol_options:
      - "no_sslv3"
    ##   - "no_tlsv1"
    max_stanza_size: 65536
    shaper: c2s_shaper
    access: c2s
  -
    port: 5269
    module: ejabberd_s2s_in
    max_stanza_size: 131072
    shaper: s2s_shaper
  ##
  ## ejabberd_service: Interact with external components (transports, ...)
  ##
  ## -
  ##   port: 8888
  ##   module: ejabberd_service
  ##   access: all
  ##   shaper_rule: fast
  ##   ip: "127.0.0.1"
  ##   hosts:
  ##     "icq.example.org":
  ##       password: "secret"
  ##     "sms.example.org":
  ##       password: "secret"

  ##
  ## ejabberd_stun: Handles STUN Binding requests
  ##
  ## -
  ##   port: 3478
  ##   transport: udp
  ##   module: ejabberd_stun

  ##
  ## To handle XML-RPC requests that provide admin credentials:
  ##
  ## -
  ##   port: 4560
  ##   module: ejabberd_xmlrpc
  ##   maxsessions: 10
  ##   timeout: 5000
  ##   access_commands:
  ##     admin:
  ##       commands: all
  ##       options: []
  -
    port: 5280
    module: ejabberd_http
    request_handlers:
      "/websocket": ejabberd_http_ws
      "/api": mod_http_api
      "/oauth": ejabberd_oauth
    ##  "/pub/archive": mod_http_fileserver
    web_admin: true
    http_bind: true
    ## register: true
    captcha: false
  -
    port: 5285
    module: ejabberd_http
    request_handlers:
       "/rest": mod_rest

###.  ==================
###'  S2S GLOBAL OPTIONS

##
## s2s_use_starttls: Enable STARTTLS + Dialback for S2S connections.
## Allowed values are: false optional required required_trusted
## You must specify a certificate file.
##
## s2s_use_starttls: optional

##
## s2s_certfile: Specify a certificate file.
##
## s2s_certfile: "/opt/ejabberd-16.06/conf/server.pem"

## Custom OpenSSL options
##
## s2s_protocol_options:
##   - "no_sslv3"
##   - "no_tlsv1"

##
## domain_certfile: Specify a different certificate for each served hostname.
##
## host_config:
##   "example.org":
##     domain_certfile: "/opt/ejabberd-16.06/conf/example_org.pem"
##   "example.com":
##     domain_certfile: "/opt/ejabberd-16.06/conf/example_com.pem"

##
## S2S whitelist or blacklist
##
## Default s2s policy for undefined hosts.
##
## s2s_access: s2s

##
## Outgoing S2S options
##
## Preferred address families (which to try first) and connect timeout
## in milliseconds.
##
## outgoing_s2s_families:
##   - ipv4
##   - ipv6
## outgoing_s2s_timeout: 10000

###.  ==============
###'  AUTHENTICATION

##
## auth_method: Method used to authenticate the users.
## The default method is the internal.
## If you want to use a different method,
## comment this line and enable the correct ones.
##
##auth_method: internal

##
## Store the plain passwords or hashed for SCRAM:
## auth_password_format: plain
## auth_password_format: scram
##
## Define the FQDN if ejabberd doesn't detect it:
## fqdn: "server3.example.com"

##
## Authentication using external script
## Make sure the script is executable by ejabberd.
##
## auth_method: external
## extauth_program: "/path/to/authentication/script"

##
## Authentication using SQL
## Remember to setup a database in the next section.
##
auth_method: odbc

##
## Authentication using PAM
##
## auth_method: pam
## pam_service: "pamservicename"

##
## Authentication using LDAP
##
## auth_method: ldap
##
## List of LDAP servers:
## ldap_servers:
##   - "localhost"
##
## Encryption of connection to LDAP servers:
## ldap_encrypt: none
## ldap_encrypt: tls
##
## Port to connect to on LDAP servers:
## ldap_port: 389
## ldap_port: 636
##
## LDAP manager:
## ldap_rootdn: "dc=example,dc=com"
##
## Password of LDAP manager:
## ldap_password: "******"
##
## Search base of LDAP directory:
## ldap_base: "dc=example,dc=com"
##
## LDAP attribute that holds user ID:
## ldap_uids:
##   - "mail": "%u@mail.example.org"
##
## LDAP filter:
## ldap_filter: "(objectClass=shadowAccount)"

##
## Anonymous login support:
##   auth_method: anonymous
##   anonymous_protocol: sasl_anon | login_anon | both
##   allow_multiple_connections: true | false
##
## host_config:
##   "public.example.org":
##     auth_method: anonymous
##     allow_multiple_connections: false
##     anonymous_protocol: sasl_anon
##
## To use both anonymous and internal authentication:
##
## host_config:
##   "public.example.org":
##     auth_method:
##       - internal
##       - anonymous

###.  ==============
###'  DATABASE SETUP

## ejabberd by default uses the internal Mnesia database,
## so you do not necessarily need this section.
## This section provides configuration examples in case
## you want to use other database backends.
## Please consult the ejabberd Guide for details on database creation.

##
## MySQL server:
##
## sql_type: mysql
## sql_server: "server"
## sql_database: "database"
## sql_username: "username"
## sql_password: "password"
##
## If you want to specify the port:
## sql_port: 1234

##
## PostgreSQL server:
##
## sql_type: pgsql
## sql_server: "server"
## sql_database: "database"
## sql_username: "username"
## sql_password: "password"
##
## If you want to specify the port:
## sql_port: 1234
##
## If you use PostgreSQL, have a large database, and need a
## faster but inexact replacement for "select count(*) from users"
##
## pgsql_users_number_estimate: true

##
## SQLite:
##
## sql_type: sqlite
## sql_database: "/opt/ejabberd-16.06/database/ejabberd.db"

##
## ODBC compatible or MSSQL server:
##
## sql_type: odbc
## sql_server: "DSN=ejabberd;UID=ejabberd;PWD=ejabberd"

sql_type: odbc
sql_server: "DSN=ejabberd;UID=#####@####-#####;PWD=#####"

##
## Number of connections to open to the database for each virtual host
##
sql_pool_size: 50

##
## Interval to make a dummy SQL request to keep the connections to the
## database alive. Specify in seconds: for example 28800 means 8 hours
##
sql_keepalive_interval: 60

###.  ===============
###'  TRAFFIC SHAPERS

shaper:
  ##
  ## The "normal" shaper limits traffic speed to 1000 B/s
  ##
  normal: 1000

  ##
  ## The "fast" shaper limits traffic speed to 50000 B/s
  ##
  fast: 50000

##
## This option specifies the maximum number of elements in the queue
## of the FSM. Refer to the documentation for details.
##
max_fsm_queue: 1000

default_db: sql

###.   ====================
###'   ACCESS CONTROL LISTS

## - "anuser@anuserRemoteVM.xny2e1cwaibufevbp43v3ynqjf.ax.internal.cloudapp.net"

acl:
  ##
  ## The 'admin' ACL grants administrative privileges to XMPP accounts.
  ## You can put here as many accounts as you want.
  ##
  admin:
    user:
      - "anuser@SOME.IP.NUMBER.A"

  ##
  ## Blocked users
  ##
  ## blocked:
  ##   user:
  ##     - "baduser@example.org"
  ##     - "test"

  ## Local users: don't modify this.
  ##
  local:
    user_regexp: ""

  ##
  ## More examples of ACLs
  ##
  ## jabberorg:
  ##   server:
  ##     - "jabber.org"
  ## aleksey:
  ##   user:
  ##     - "aleksey@jabber.ru"
  ## test:
  ##   user_regexp: "^test"
  ##   user_glob: "test*"

  ##
  ## Loopback network
  ##
  loopback:
    ip:
      - "127.0.0.0/8"

  ##
  ## Bad XMPP servers
  ##
  ## bad_servers:
  ##   server:
  ##     - "xmpp.zombie.org"
  ##     - "xmpp.spam.com"

##
## Define specific ACLs in a virtual host.
##
## host_config:
##   "localhost":
##     acl:
##       admin:
##         user:
##           - "bob-local@localhost"

access:
  configure:
    admin: allow

###.  ============
###'  SHAPER RULES

shaper_rules:
  ## Maximum number of simultaneous sessions allowed for a single user:
  max_user_sessions: 10
  ## Maximum number of offline messages that users can have:
  max_user_offline_messages:
    - 5000: admin
    - 100
  ## For C2S connections, all users except admins use the "normal" shaper
  c2s_shaper:
    - none: admin
    - normal
  ## All S2S connections use the "fast" shaper
  s2s_shaper: fast

###.  ============
###'  ACCESS RULES
access_rules:
  ## This rule allows access only for local users:
  local:
    - allow: local
  ## Only non-blocked users can use c2s connections:
  c2s:
    - deny: blocked
    - allow
  ## Only admins can send announcement messages:
  announce:
    - allow: admin
  ## Only admins can use the configuration interface:
  configure:
    - allow: admin
  ## Only accounts of the local ejabberd server can create rooms:
  muc_create:
    - allow: local
  ## Only accounts on the local ejabberd server can create Pubsub nodes:
  pubsub_createnode:
    - allow: local
  ## In-band registration allows registration of any possible username.
  ## To disable in-band registration, replace 'allow' with 'deny'.
  register:
    - allow
  ## Only allow to register from localhost
  trusted_network:
    - allow: loopback
  ## Do not establish S2S connections with bad servers
  ## s2s:
  ##   - deny:
  ##     - ip: "XXX.XXX.XXX.XXX/32"
  ##   - deny:
  ##     - ip: "XXX.XXX.XXX.XXX/32"
  ##   - allow

## By default the frequency of account registrations from the same IP
## is limited to 1 account every 10 minutes. To disable, specify: infinity
## registration_timeout: 600
 
##
## Define specific Access Rules in a virtual host.
##
## host_config:
##   "localhost":
##     access:
##       c2s:
##         - allow: admin
##         - deny
##       register:
##         - deny

###.  ================
###'  DEFAULT LANGUAGE

##
## language: Default language used for server messages.
##
language: "en"

##
## Set a different default language in a virtual host.
##
## host_config:
##   "localhost":
##     language: "ru"

###.  =======
###'  CAPTCHA

##
## Full path to a script that generates the image.
##
## captcha_cmd: "/opt/ejabberd-16.06/lib/ejabberd-16.06/priv/bin/captcha.sh"

##
## Host for the URL and port where ejabberd listens for CAPTCHA requests.
##
## captcha_host: "anuserRemoteVM.xny2e1cwaibufevbp43v3ynqjf.ax.internal.cloudapp.net:5280"

##
## Limit CAPTCHA calls per minute for JID/IP to avoid DoS.
##
## captcha_limit: 5

###.  =======
###'  MODULES

##
## Modules enabled in all ejabberd virtual hosts.
##
modules:
  mod_adhoc: {}
  mod_admin_extra: {}
  mod_announce: # recommends mod_adhoc
    access: announce
  mod_blocking: {} # requires mod_privacy
  mod_caps: {}
  mod_carboncopy:
    db_type: mnesia
  mod_client_state: {}
  mod_configure: {} # requires mod_adhoc
  mod_disco: {}
  ## mod_echo: {}
  mod_http_bind: {}
  ## mod_http_fileserver:
  ##   docroot: "/var/www"
  ##   accesslog: "/opt/ejabberd-16.06/logs/access.log"
  ## mod_irc: {}
  mod_last: {}
  mod_muc:
    ## host: "conference.@HOST@"
    access:
      - allow
    access_admin:
      - allow: admin
    access_create: muc_create
    access_persistent: muc_create
  mod_muc_admin: {}
  ## mod_muc_log: {}
  ## mod_multicast: {}
  mod_offline:
    access_max_user_messages: max_user_offline_messages
    db_type: odbc
  mod_ping: {}
  ## mod_pres_counter:
  ##   count: 5
  ##   interval: 60
  mod_privacy: {}
  mod_private: {}
  ## mod_proxy65: {}
  mod_pubsub:
    access_createnode: pubsub_createnode
    ## reduces resource comsumption, but XEP incompliant
    ignore_pep_from_offline: true
    ## XEP compliant, but increases resource comsumption
    ## ignore_pep_from_offline: false
    last_item_cache: false
    plugins:
      - "flat"
      - "hometree"
      - "pep" # pep requires mod_caps
  mod_register:
    ##
    ## Protect In-Band account registrations with CAPTCHA.
    ##
    ##   captcha_protected: true
    ##
    ## Set the minimum informational entropy for passwords.
    ##
    ##   password_strength: 32
    ##
    ## After successful registration, the user receives
    ## a message with this subject and body.
    ##
    welcome_message:
      subject: "Welcome!"
      body: |-
        Hi.
        Welcome to this XMPP server.
    ##
    ## When a user registers, send a notification to
    ## these XMPP accounts.
    ##
    ##   registration_watchers:
    ##     - "admin1@example.org"
    ##
    ## Only clients in the server machine can register accounts
    ##
    ip_access: trusted_network
    ##
    ## Local c2s or remote s2s users cannot register accounts
    ##
    ##   access_from: deny
    access: register
  mod_roster:
   db_type: sql
  mod_shared_roster: {}
  ## mod_stats: {}
  ## mod_time: {}
  mod_vcard:
    search: false
  mod_version: {}
  mod_rest:
    allowed_ips:
     - "all"
    access_commands:
      restaccess:
        - registered_users
        - connected_users
  mod_mam:
    db_type: mnesia
    default: always
  mod_offline_post:
    auth_token: "veryverylongkey"
    post_url: "http://SOME.IP.NUMBER.A:3000/offline"
##
## Enable modules with custom options in a specific virtual host
##
## host_config:
##   "localhost":
##     modules:
##       mod_echo:
##         host: "mirror.localhost"

##
## Enable modules management via ejabberdctl for installation and
## uninstallation of public/private contributed modules
## (enabled by default)
##

allow_contrib_modules: true

###.
###'
### Local Variables:
### mode: yaml
### End:
### vim: set filetype=yaml tabstop=8 foldmarker=###',###. foldmethod=marker:

commands_admin_access: configure
commands:
  - add_commands:
    - status
    - register
    - unregister
    - change_password
    - add_rosteritem
oauth_expire: 950400
oauth_access: all

up

up

Syndicate content