Windows Internal Database Integration and Synchronization

 

Windows Internal Database, which is a variant of SQL Server Express, can be integrated and synchronized codelessly with 100+ external systems, on-premises or in the cloud, using the Layer2 Cloud Connector, for example with Office 365, SharePoint, Dynamics, Exchange or Azure. You will find more supported systems and applications here.​​​​​

Windows Internal Database Specific Configuration Settings

 

In the Layer2 Cloud Connector, the data source entity must be configured as follows to connect to a Windows Internal Database.

 

Windows-Internal-DB-Integration-Synchronization

 Fig. Windows Internal Database example connection in the Layer2 Layer2 Cloud Connector

 

 

Please note the following about settings, features, and possible issues.

 

  • The Windows Internal Database has a number of restrictions and special considerations for being accessed. Please see the "Known Issues and Workaround" section below for a full list.
  • You must install the Layer2 Cloud Connector on the machine that has the database - this database cannot be accessed remotely.
  • Please select the .Net Framework Data Provider for SqlServer from the list of installed providers. It should already be installed with Windows, but if it is not, you can install it from Microsoft.
  • You must these specific instance names to connect to the database, based on which version of Windows Server the host machine is running:

    Server 2003-2008
    Data Source=\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query; Initial Catalog=myDatabase; Integrated security=SSPI;

    Server 2012
    Data Source=\\.\pipe\MICROSOFT##WID\tsql\query; Initial Catalog=myDatabase; Integrated security=SSPI;
  • As the user necessary to access the database is usually the administrator account that installed Windows, it is recommended that you use integrated security so that you can use Windows Authentication with the Administrator. This can lead to some additional issues with the service - see the "Known Issues and Workaround" section below for more information.
  • You can make use of all SQL queries your data provider supports. You can also use stored procedures that returns records, exec myStoredProcedure().
  • You can make use of aliases in query to force auto-mapping in the Cloud Connector, e.g. select customername as title, to auto-map to a SharePoint title column.
  • The primary key is normally set automatically based on the table settings.
  • While the Bi-directional sync is technically supported with this provider, it is not recommended. Please only use this data source as read-only. See the "Known Issues and Workaround" section below for more information.

 

Do you have any issues to connect? Please contact [email protected] for next steps.

Windows Internal Database Integration - Known Issues and Workarounds

 

Due to the special nature of the Windows Internal Database, it is only recommended that you access it for backups or for one-way synchronizing the data. It should not be used as a "normal" database - there's SQL Express and other free SQL-based databases out there for that. Please note the various restrictions and considerations below:

 

  • The database cannot be reached remotely. Therefore the Cloud Connector app must be installed on the machine with the Windows Internal Database you wish to use.​
  • Bi-directional synchronization is supported, but not recommended for the following reasons: Modifying the database can possibly break functionality with the Windows features and apps that use it; product service packs may overwrite your changes; the features and apps may overwrite your changes. Please only use this as a read-only source.
  • It is recommended that you use the Windows administrator account with integrated security (SQL accounts may not work). Other general user and service accounts may also not work to access the databases within WID.
  • When using integrated security in the connection string, the scheduling service (in versions 7.7 or older) and the backend service (in versions 7.8+) will request a service account change to an account that has access to this database. This may require that you use the administrator account for the service, which may be a security concern.
  • Additional information about accessing the Windows Internal Database may be found here and here.
  • You will learn more about the SQL query language here.

Step-by-Step Intros For Windows Internal Database Integration Scenarios

 

​You will find more information about specific scenarios here:

 

Can't find the scenario you are looking for? Please contact [email protected] for help.

Find related solutions here.

Learn more about similar solutions for data migration, backup, integration, and synchronization by vendor and topic.

Ready to go next steps?

Ifoc for free trial product regsitration - Layer2 leading solutions

Register for free download.

Keep your Sharepoint in sync. Download and try today.

Contact Us Icon for Layer2 leading solutions

Questions? Contact us.

We are here to help. Contact us and out consulting will be happy to answer your questions.