SQL Server connection error

Question

SQL Server connection error

Opened this issue 5 years ago · 5 comments

Hi Mark,

I cloned the repository at https://github.com/markvanaalst/SXA.Styleguide and followed the docker installation instructions.

On accessing http://localhost:44001/unicorn.aspx I get the following error:

[Win32Exception (0x80004005): The network path was not found]
[SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)]

I did "docker-compose up" to see output.

On request http://localhost:44001/unicorn.aspx I get the following output:

cm_1    | 2020-03-25 01:46:12.766 [system]       INFO  (2940) HttpModule is being initialized
cm_1    | 2020-03-25 01:46:13.258 [system]       INFO  (2940) Cache created: 'DeviceItemsCache' (max size: 50MB, running total: 50MB)
cm_1    | 2020-03-25 01:46:13.275 [system]       INFO  (2940) Cache created: 'master[data]' (max size: 100MB, running total: 150MB)
cm_1    | 2020-03-25 01:46:13.287 [system]       INFO  (2940) Cache created: 'master[items]' (max size: 50MB, running total: 200MB)
cm_1    | 2020-03-25 01:46:13.290 [system]       INFO  (2940) Cache created: 'master[paths]' (max size: 2MB, running total: 202MB)
cm_1    | 2020-03-25 01:46:13.291 [system]       INFO  (2940) Cache created: 'master[standardValues]' (max size: 2MB, running total: 204MB)
cm_1    | 2020-03-25 01:46:13.292 [system]       INFO  (2940) Cache created: 'master[languageFallbackObsolete]' (max size: 20MB, running total: 224MB)
cm_1    | 2020-03-25 01:46:13.293 [system]       INFO  (2940) Cache created: 'master[languageFallback]' (max size: 20MB, running total: 244MB)
cm_1    | 2020-03-25 01:46:13.308 [system]       INFO  (2940) Cache created: 'master[isLanguageFallbackValid]' (max size: 20MB, running total: 264MB)
cm_1    | 2020-03-25 01:46:13.309 [system]       INFO  (2940) Cache created: 'master[isLanguageFallbackValidObsolete]' (max size: 1MB, running total: 265MB) cm_1    | 2020-03-25 01:46:13.310 [system]       INFO  (2940) Cache created: 'master[itempaths]' (max size: 50MB, running total: 315MB)
cm_1    | 2020-03-25 01:46:13.315 [system]       INFO  (2940) Cache created: 'master[blobIDs]' (max size: 5MB, running total: 320MB)
cm_1    | 2020-03-25 01:46:30.376 [system]       INFO  (2940) Request to database failed, retry 1
cm_1    | 2020-03-25 01:46:30.592 [system]       INFO  (2940) Request to database failed, retry 2
cm_1    | 2020-03-25 01:46:30.810 [system]       INFO  (2940) Request to database failed, retry 3
cm_1    | 2020-03-25 01:46:31.024 [system]       INFO  (2940) Request to database failed, retry 4
cm_1    | 2020-03-25 01:46:31.240 [system]       INFO  (2940) Request to database failed, retry 5
cm_1    | 2020-03-25 01:46:41.285 [system]       INFO  (2552) **************************************************
cm_1    | 2020-03-25 01:46:41.289 [system]       WARN  (2552) Sitecore shutting down
cm_1    | 2020-03-25 01:46:41.289 [system]       WARN  (2552) Shutdown message: Initialization Error
cm_1    | HostingEnvironment initiated shutdown

Any idea how to debug this issue?

Answer 1 · 2020-03-25T20:36:52.000Z

I can't seem to replicate the issue on my dev environment. Will check on another laptop

Answer 2 · 2020-03-25T21:46:49.000Z

It is strange, I can access the sql server and Solr, but connection between Sitecore and sql fails Met vriendelijke groet / regards, Serge van den Oever

…

________________________________ From: Mark van Aalst <notifications@github.com> Sent: Wednesday, March 25, 2020 9:37:06 PM To: markvanaalst/SXA.Styleguide <SXA.Styleguide@noreply.github.com> Cc: Serge van den Oever <Serge.van.den.Oever@macaw.nl>; Author <author@noreply.github.com> Subject: Re: [markvanaalst/SXA.Styleguide] SQL Server connection error (#4) I can't seem to replicate the issue on my dev environment. Will check on another laptop — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub<#4 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AAB4IOTHH753L7X6N33C2Q3RJJTPFANCNFSM4LTC5QGA>.

Answer 3 · 2020-03-27T14:21:44.000Z

@svdoever can you try without the SQL persistent volume map? Its look like '.\data\sql' cannot be attached to your local disk.

services:
  sql:
    image: ${REGISTRY}sitecore-xm-sxa-sqldev:${SITECORE_VERSION}-windowsservercore-${WINDOWSSERVERCORE_VERSION}
    # volumes:
    #  - .\data\sql:C:\Data**
    mem_limit: 2GB
    ports:
      - "44010:1433"
    environment:
      SA_PASSWORD: ${SQL_SA_PASSWORD}
      ACCEPT_EULA: "Y"

Answer 4 · 2020-03-29T13:25:36.000Z

@bplasmeijer thanks for looking into this issue. I tried your suggestion, but I get the same error.

My docker-compose.yml file nog looks like:

version: "2.4"

services:
  sql:
    image: ${REGISTRY}sitecore-xm-sxa-sqldev:${SITECORE_VERSION}-windowsservercore-${WINDOWSSERVERCORE_VERSION}
    # volumes:
    #  - .\data\sql:C:\Data
    mem_limit: 2GB
    ports:
      - "44010:1433"
    environment:
      SA_PASSWORD: ${SQL_SA_PASSWORD}
      ACCEPT_EULA: "Y"

  solr:
    image: ${REGISTRY}sitecore-xm-sxa-solr:${SITECORE_VERSION}-nanoserver-${NANOSERVER_VERSION}
    volumes:
      - .\data\solr:C:\Data
    mem_limit: 1GB
    ports:
      - "44011:8983"
    environment:
      SOLR_LOG_LEVEL: ERROR

  cm:
    image: ${REGISTRY}sitecore-xm-sxa-cm:${SITECORE_VERSION}-windowsservercore-${WINDOWSSERVERCORE_VERSION}
    entrypoint: powershell.exe -NoLogo -NoProfile -File C:\\tools\\entrypoints\\iis\\Development.ps1
    volumes:
      - .\deploy:C:\src
      - .\items:C:\inetpub\wwwroot\App_Data\Unicorn\sxa-styleguide
      - C:\license:C:\license:ro 
    ports:
      - "44001:80"
      - "44002:443"
    environment:
      SITECORE_LICENSE_LOCATION: C:\license\license.xml
      SITECORE_APPSETTINGS_SXAXM:DEFINE: sxaxmonly
      SITECORE_APPSETTINGS_ROLE:DEFINE: ContentManagement, Indexing
      SITECORE_CONNECTIONSTRINGS_CORE: Data Source=sql;Initial Catalog=Sitecore.Core;User ID=sa;Password=${SQL_SA_PASSWORD}
      SITECORE_CONNECTIONSTRINGS_SECURITY: Data Source=sql;Initial Catalog=Sitecore.Core;User ID=sa;Password=${SQL_SA_PASSWORD}
      SITECORE_CONNECTIONSTRINGS_MASTER: Data Source=sql;Initial Catalog=Sitecore.Master;User ID=sa;Password=${SQL_SA_PASSWORD}
      SITECORE_CONNECTIONSTRINGS_WEB: Data Source=sql;Initial Catalog=Sitecore.Web;User ID=sa;Password=${SQL_SA_PASSWORD}
      SITECORE_CONNECTIONSTRINGS_EXPERIENCEFORMS: Data Source=sql;Initial Catalog=Sitecore.ExperienceForms;User ID=sa;Password=${SQL_SA_PASSWORD}
      SITECORE_CONNECTIONSTRINGS_SOLR.SEARCH: http://solr:8983/solr
      SITECORE_APPSETTINGS_TELERIK.ASYNCUPLOAD.CONFIGURATIONENCRYPTIONKEY: ${TELERIK_ENCRYPTION_KEY}
      SITECORE_APPSETTINGS_TELERIK.UPLOAD.CONFIGURATIONHASHKEY: ${TELERIK_ENCRYPTION_KEY}
      SITECORE_APPSETTINGS_TELERIK.WEB.UI.DIALOGPARAMETERSENCRYPTIONKEY: ${TELERIK_ENCRYPTION_KEY}
      ENTRYPOINT_STDOUT_IIS_ACCESS_LOG_ENABLED: 'false'
      ENTRYPOINT_STDOUT_IIS_ERROR_LOG_ENABLED: 'false'
      ENTRYPOINT_STDOUT_SITECORE_LOG_ENABLED: 'true'
    depends_on:
      sql:
        condition: service_healthy
      solr:
        condition: service_started

networks:
  default:
    external:
      name: nat

I can connect to the SQL server using:

Server name: localhost, 44010
Login: sa
Password: the long password as defined in the .env file

In the database all tables exist.

Answer 5 · 2020-03-31T09:47:27.000Z

@bplasmeijer I tried everything, created a minimal docker-compose file, updated it to version 3.7, defined custom networks, added its own subnet, using fixed ip addresses - nothing solved the issue... I have no idea what to do next...

My last try was this:

version: "3.7"

services:
  sql:
    image: ${REGISTRY}sitecore-xp-sxa-jss-sqldev:${SITECORE_VERSION}-windowsservercore-${WINDOWSSERVERCORE_VERSION}
    volumes:
      - .\data\sql:C:\Data
    networks:
      app_net:
        ipv4_address: 172.16.238.10
    deploy:
      resources:
        limits:
          memory: 2GB
    ports:
      - "44010:1433"
    environment:
      SA_PASSWORD: ${SQL_SA_PASSWORD}
      ACCEPT_EULA: "Y"

  solr:
    image: ${REGISTRY}sitecore-xp-sxa-solr:${SITECORE_VERSION}-nanoserver-${NANOSERVER_VERSION}
    volumes:
      - .\data\solr:C:\Data
    networks:
      app_net:
        ipv4_address: 172.16.238.11
    deploy:
      resources:
        limits:
          memory: 1GB

    ports:
      - "44011:8983"

  xconnect:
    image: ${REGISTRY}sitecore-xp-xconnect:${SITECORE_VERSION}-windowsservercore-${WINDOWSSERVERCORE_VERSION}
    volumes:
      - .\data\xconnect:C:\inetpub\wwwroot\App_Data\logs
    deploy:
      resources:
        limits:
          memory: 1GB
    environment:
      SITECORE_LICENSE: ${SITECORE_LICENSE}
      SITECORE_SITECORE:XCONNECT:COLLECTIONSEARCH:SERVICES:SOLR.SOLRREADERSETTINGS:OPTIONS:REQUIREHTTPS: "false"
      SITECORE_SITECORE:XCONNECT:SEARCHINDEXER:SERVICES:SOLR.SOLRWRITERSETTINGS:OPTIONS:REQUIREHTTPS: "false"
      SITECORE_CONNECTIONSTRINGS_MESSAGING: Server=172.16.238.10,1433;Database=Sitecore.Messaging;User ID=sa;Password=${SQL_SA_PASSWORD}
      SITECORE_CONNECTIONSTRINGS_PROCESSING.ENGINE.STORAGE: Server=172.16.238.10,1433;Database=Sitecore.ProcessingEngineStorage;User ID=sa;Password=${SQL_SA_PASSWORD}
      SITECORE_CONNECTIONSTRINGS_REPORTING: Server=172.16.238.10,1433;Database=Sitecore.Reporting;User ID=sa;Password=${SQL_SA_PASSWORD}
      SITECORE_CONNECTIONSTRINGS_XDB.MARKETINGAUTOMATION: Server=172.16.238.10,1433;Database=Sitecore.MarketingAutomation;User ID=sa;Password=${SQL_SA_PASSWORD}
      SITECORE_CONNECTIONSTRINGS_XDB.PROCESSING.POOLS: Server=172.16.238.10,1433;Database=Sitecore.Processing.Pools;User ID=sa;Password=${SQL_SA_PASSWORD}
      SITECORE_CONNECTIONSTRINGS_XDB.REFERENCEDATA: Server=172.16.238.10,1433;Database=Sitecore.ReferenceData;User ID=sa;Password=${SQL_SA_PASSWORD}
      SITECORE_CONNECTIONSTRINGS_COLLECTION: Server=172.16.238.10,1433;Database=Sitecore.Xdb.Collection.ShardMapManager;User ID=sa;Password=${SQL_SA_PASSWORD}
      SITECORE_CONNECTIONSTRINGS_SOLRCORE: http://solr:8983/solr/sitecore_xdb
    depends_on:
      - sql
      - solr

  cm:
    image: ${REGISTRY}sitecore-xp-sxa-jss-standalone:${SITECORE_VERSION}-windowsservercore-${WINDOWSSERVERCORE_VERSION}
    entrypoint: powershell.exe -Command "& C:\\tools\\entrypoints\\iis\\Development.ps1"
    ports:
      - "44001:80"
    environment:
      SITECORE_LICENSE: ${SITECORE_LICENSE}
      SITECORE_APPSETTINGS_ROLE:DEFINE: Standalone
      SITECORE_APPSETTINGS_SXAXM:DEFINE: sxaxconnect
      SITECORE_CONNECTIONSTRINGS_CORE: Server=172.16.238.10,1433;Initial Catalog=Sitecore.Core;User ID=sa;Password=${SQL_SA_PASSWORD}
      SITECORE_CONNECTIONSTRINGS_SECURITY: Server=172.16.238.10,1433;Initial Catalog=Sitecore.Core;User ID=sa;Password=${SQL_SA_PASSWORD}
      SITECORE_CONNECTIONSTRINGS_MASTER: Server=172.16.238.10,1433;Initial Catalog=Sitecore.Master;User ID=sa;Password=${SQL_SA_PASSWORD}
      SITECORE_CONNECTIONSTRINGS_WEB: Server=172.16.238.10,1433;Initial Catalog=Sitecore.Web;User ID=sa;Password=${SQL_SA_PASSWORD}
      SITECORE_CONNECTIONSTRINGS_EXPERIENCEFORMS: Server=172.16.238.10,1433;Initial Catalog=Sitecore.ExperienceForms;User ID=sa;Password=${SQL_SA_PASSWORD}
      SITECORE_CONNECTIONSTRINGS_SOLR.SEARCH: http://172.16.238.11:8983/solr
      SITECORE_CONNECTIONSTRINGS_MESSAGING: Server=172.16.238.10,1433;Database=Sitecore.Messaging;User ID=sa;Password=${SQL_SA_PASSWORD}
      SITECORE_CONNECTIONSTRINGS_XDB.MARKETINGAUTOMATION: Server=172.16.238.10,1433;Database=Sitecore.MarketingAutomation;User ID=sa;Password=${SQL_SA_PASSWORD}
      SITECORE_CONNECTIONSTRINGS_XDB.PROCESSING.POOLS: Server=172.16.238.10,1433;Database=Sitecore.Processing.Pools;User ID=sa;Password=${SQL_SA_PASSWORD}
      SITECORE_CONNECTIONSTRINGS_XDB.REFERENCEDATA: Server=172.16.238.10,1433;Database=Sitecore.ReferenceData;User ID=sa;Password=${SQL_SA_PASSWORD}
      SITECORE_CONNECTIONSTRINGS_XDB.PROCESSING.TASKS: Server=172.16.238.10,1433;Database=Sitecore.Processing.Tasks;User ID=sa;Password=${SQL_SA_PASSWORD}
      SITECORE_CONNECTIONSTRINGS_EXM.MASTER: Server=172.16.238.10,1433;Database=Sitecore.EXM.Master;User ID=sa;Password=${SQL_SA_PASSWORD}
      SITECORE_CONNECTIONSTRINGS_REPORTING: Server=172.16.238.10,1433;Database=Sitecore.Reporting;User ID=sa;Password=${SQL_SA_PASSWORD}
      SITECORE_CONNECTIONSTRINGS_SITECORE.REPORTING.CLIENT: http://xconnect
      SITECORE_CONNECTIONSTRINGS_XCONNECT.COLLECTION: http://xconnect
      SITECORE_CONNECTIONSTRINGS_XDB.MARKETINGAUTOMATION.OPERATIONS.CLIENT: http://xconnect
      SITECORE_CONNECTIONSTRINGS_XDB.MARKETINGAUTOMATION.REPORTING.CLIENT: http://xconnect
      SITECORE_CONNECTIONSTRINGS_XDB.REFERENCEDATA.CLIENT: http://xconnect
      SITECORE_APPSETTINGS_TELERIK.ASYNCUPLOAD.CONFIGURATIONENCRYPTIONKEY: ${TELERIK_ENCRYPTION_KEY}
      SITECORE_APPSETTINGS_TELERIK.UPLOAD.CONFIGURATIONHASHKEY: ${TELERIK_ENCRYPTION_KEY}
      SITECORE_APPSETTINGS_TELERIK.WEB.UI.DIALOGPARAMETERSENCRYPTIONKEY: ${TELERIK_ENCRYPTION_KEY}
    depends_on:
      - sql
      - solr
      - xconnect

networks:
  app_net:
    ipam:
      driver: default
      config:
        - subnet: "172.16.238.0/24"