Blog Engine and SQL Server
March 30, 2010 1 Comment
I spent my free time over the weekend doing taxes and family stuff, so I did not get to any code. I did start working with BlogEngine to see if I can incorporate it into our website. I ran into an interesting problem. I wanted to change the data provider from an XML file in the app_data folder to a SQL Server database. I ran the scripts no problem on a local instance of SQL Server. I then created a SQL Server Login using SQL Server Authentication:
I then went to create a connection to the database via Visual Studio using a SQL Server Data Provider in a basic ASP.NET application:
And I get an error:
I then went out to SQL Server Management Studio and I also cannot login using those credentials. I don’t know if there is something about Vista and SQL Server Authentication – I will continuing researching it.
I then changed the server role to sysadmin:
Changing the network library didn’t work:
I then tried starting all of the SQL Server related services – still no dice
Finally, I then made sure that Sql Server Configuration allowed TCP and Shared Memory – it did
Then, in the words of Charlie Brown “There is no problem so large that you cannot run away from it”, I figured I would just use integrated security.
I changed the connection string in my web.config to this:
<add name="BlogEngine" connectionString="Server=.;Database=BlogEngine;Trusted_Connection=True;" providerName="System.Data.SqlClient"/>
And I added this line:
<identity impersonate="true" userName="xxx" password="yyy" />
I still got a login error regarding NT AUTHORITY\NETWORK SERVICE. I then decided that if I couldn’t beat them, make my solution less secure. I added NT AUTHORITY\NETWORK SERVICE to the server and database as sysadmin server role and datawriter and datareader database role.
It worked. I am not happy, but it worked…