Skip to main content
Documentation & User Guides | Fotoware

Database settings in detail

  1. Last updated
  2. Save as PDF

In-depth article that covers the authentication modes in SQL server and how to configure FotoWeb to work with SQL servers on another host.

General information

There are two authentication methods available when connecting to a SQL Server:

  • SQL Server Authentication: A user that only resides inside SQL Server, you have to provide a username and password.
  • Windows Authentication: Authenticate by OS, the user is a Windows or a Domain user.

For more detailed information, see http://msdn.microsoft.com/en-us/library/ms144284.aspx

 

When using a “Trusted Connection” in the Database Settings in FotoWeb or during FotoWeb installation, the connection to MSSQL Server doesn't involve sending the username and password - it means we are using Windows authentication with FotoWeb’s process account (effectively the process account that is specified in the Operations Center).

 

When not using a trusted connection, a username and password must be specified. The user you enter here must be a MSSQL Server login and not a Windows account. FotoWeb then uses a SQL Server authentication method. If you supply a Windows or domain user in the username and password fields in this case, you will get the following error message on the client; "Login failed for user 'domain\username'".

 

To investigate further what causes the error message, the SQL Server log files will give you more details; "Login failed for user 'domain\username'. Reason: Attempting to use an NT account name with SQL Server Authentication".

 

To sum up, when not using a trusted connection, a MSSQL Server login must be specified and not a Windows/Domain user.

FotoWeb and MSSQL Server on different machines

If the FotoWeb server and the MSSQL Server are in the same domain but not on the same machine and you want to use a trusted connection, you have to use a domain account. A Windows account won't work across machines in this way easily. The process account for FotoWeb should then be a domain account. The same domain user must be added as a Windows login in MSSQL Server Management Console.

Now, when a trusted connection is being made to the SQL Server the domain account has access and exists on both sides of the authentication process.

What access must FotoWeb’s database user account have?

FotoWeb needs a database user to read and write data to the database on a daily basis. The MSSQL Server Role this user needs is a Public Server Role, as well as the three following roles;

  • db_datareader
  • db_datawriter
  • db_ddladmin

What access must FotoWeb’s database administrator account have?

When creating a MSSQL Server login to use for administrative tasks in FotoWeb, when asked during the installation or upgrade process, or when you select Create and verify Database in FotoWeb Configuration, the account must be part of the sysadmin Server Role. This user is used to create the database if it is not already created.

Sysadmin rights are required during FotoWeb setup and when updating FotoWeb to a more recent version. If this is not acceptable, it is possible to manually run the SQL scripts to create the database structure under dbo permissions. These scripts are available in the Server settings folder in the Fotoware folder in Windows ProgramData. Some of these scripts will have to be run each time FotoWeb is updated to a new version.

Database settings during installation with a standalone SQL Server

When performing a fresh installation of FotoWeb, you have the possibility to specify a database user, database administrator, or use a trusted connection. The user you specify for the database user will be created if it doesn’t exist. If you select trusted connection for the database user, then FotoWeb’s process account will be created as a Windows-authenticated user on the SQL Server. The user you specify for the database administrator must already exist as a SQL Server Login before proceeding beyond this point in the installation. Credentials for the database administrator are not permanently stored - they are used to create the database tables. The credentials will be stored if you also enable automatic database backup.

NOTE: If you select to use trusted connection for the database administrator, make sure that FotoWeb’s process account has sysadmin access on the SQL Server before you proceed. From SQL Server 2008 and later the Windows Group BUILTIN\Administrator is no longer included in the SQL Server sysadmin fixed Server Role on new SQL Server 2008 installations.

Changing from trusted connection to specifying a database user and vice versa

If you have installed FotoWeb and later find out that you need to change the database settings you can do so in the FotoWeb Configuration under Settings – Site – Database. If you change the database user, select Create and verify database to create the user. If the user doesn’t have access to the database, selecting Test connection will return an error message saying so.