I’ve been involved in setting up a Biztalk 2013 R2 instance, and it’s been a good learning opportunity for me. We ran into an interesting issue today, having to do with SQL Server permissions. The environment I work in is pretty locked down as far as permissions are concerned, and in trying to deploy a Biztalk application to the development environment and on entering the database server name in the Server field, the database could not be selected. If the defaults were supplied by default, everything was OK. But on entering the server name, the selector for the Biztalk Configuration database said that it couldn’t connect to the server to retrieve the database list. It failed to pull the names of the databases available on the server for my AD account.
While some sites said adding sysadmin in SQL Server would help, what actually worked was granting the “View All Databases” permission in SQL Server. So by adding this database permission, that resolved the issue. I was glad it was that because I wasn’t about to get sysadmin privileges :-D.