I am having an issue with connecting to the database for KHouse through sharepoint. I have run through several workarounds to get it to work, but to no avail.
We are using several pages that retrieve information from the database. The database is on a separate server. In the web.config we have specified the connection string. We are only allowed to connect to the database with a service account (we are not allowed to use sql server authentication). Every user should be able to connect using this service account.
This is what we tried:
using impersonation, where we supply a username and password/ and a connectionstring with Integrated security=sspi/Persist security info=true. Integrated Security=SSPI; Persist Security Info=True;
this gives us access to the database, but every user is then accessing sharepoint with the service account. this is not what we need, because every user should be recognised by sharepoint with their own account.
using connectionstring, where we supply username/password/Integrated security=true/Persist security info=true Trusted_Connection=True;
this gives us an error, probably because of the double-hop issue (database on other server): Error: 18456, Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. Reason: Token-based server access validation failed with an infrastructure error. Just to be clear: we are not allowed to give 'NT AUTHORITY\ANONYMOUS LOGON’ access to the database. so we cannot go that route.
several other configurations of the connectionstring like Trusted_Connection=True etc...
On the database side everything is setup correctly: - We added the service account as a Login in SQL Server - The login has access to the right tables with the right permissions
web.config sections we use; --> ;Initial Catalog=KHouse;Integrated Security=SSPI; Persist Security Info=True;" providerName="System.Data.SqlClient" />
Do you have an idea how we can get a connection to the database using the service account, but with users authenticating as themselves in sharepoint?