Setup Requiring SQL Auth?

Topics: Administration, Installing Orchard
Nov 22, 2015 at 3:26 AM
I've been wrestling with getting Orchard 1.9.2 installed and running in a dev environment as well as in Azure. Set up fails with "Object reference not set to an instance of an object" almost immediately after clicking the "Finish Setup" button. I tracked it down to not being able to connect to the database - _session.Connection is null. SQL Profiler clued me in that SQL Auth was not enabled on my SQL Server, so I tried enabling it and creating a new user. Lo and behold, Orchard started working. This doesn't seem right, and I don't think is even supported by Azure.

I've tried various incarnations of these connection strings:
Data Source=myServer;Initial Catalog=Orchard;Persist Security Info=True;User ID=myServer\OrchDB;Password=myPassword

Server=tcp:myServer.database.windows.net,1433;Database=orchard;User ID=OrchDBAdmin@myServer;Password=myPassword;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;
The second connection string is right from Azure. The first is based off of the example shown by Orchard on the Setup screen.

What am I missing/doing wrong??

Thanks.
Nov 22, 2015 at 9:51 AM
So your local SQL was only working with local account without the uid/pwd combination, and you set your connection string to use uid/pwd, so it didn't work, then enabling SQL to accept creds, now it work? did I get it right? and what's wrong with that?
Nov 22, 2015 at 4:06 PM
Sorry, I wasn't clear. Whether I use a domain or a local machine account in the connection string on my local dev environment, somehow SQL Server seems to try and authenticate it as a SQL Server account (SQL Authentication). This does not work. However, if I enable mixed mode auth in SQL Server and create a SQL Server login, then use that in my connection string it works fine. The problem is that I see the same thing in Azure for my production site, and Azure does not allow me to enable SQL Auth on my database.