Please tell me there is a way to use integrated security across to boxes!
I have an intranet server that runs both IIS and SQL Server. My ASP applications make use of Integrated windows authentication, so that I can set permissions not only on the ASP pages themselves, but also on objects within SQL Server.
I recently obtained another server on which I have installed SQL Server. I want to move all the databases on my original box on to the new one and have two servers - one to server the pages, and one to store the data.
I thought it would be a simple case of changing the connection string from something like:
sCn="Provider=SQLOLEDB; Data Source=ORIGINAL; Initial Catalog=dbDatabase; Integrated Security=SSPI"
sCn="Provider=SQLOLEDB; Data Source=NEW; Initial Catalog=dbDatabase; Integrated Security=SSPI"
However, after getting the error message:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
..I started looking around on the web and found out that it is impossible to use windows authentication between an IIS box and a SQL Server box, even if they are both on the same domain!!
Can someone please tell me I'm stupid, and show me how to do it!?