Connecting to SQL database

Hi all,

Please help me with the following problem because I am running around in circles.

I have this problem getting a forum online.

Since it is a database related problem which I have been trying to solve at the ClubSite forum I am taking the liberty to come to this forum with this problem.

This is the story:

I have this separate SQL databasedotForum2004.mdf in App_data.

After trial and error got it working locally.

But when I put it online at my webserver (On a Stand-alone PC at my jobsite) I get an application error.(From the Club Site application)

This is the connection key which works fine locally. Is there something obvious you can point me at?

<addkey="forumDSN"value="Provider=SQLOLEDB; Server=.\SQLExpress;AttachDbFilename=|DataDirectory|dotForum2004.mdf;Trusted_Connection=Yes;" />

Thanks in advance,

Lex

Hi Lexy,

From you connection string I notice you use Windows Authentication (Trusted_Connection=Yes) to connect to database. And as I know when the project is deployed to website, it will run under an account (maybe called 'NT AUTHROTY\NETWORK SERVICE' or something like this, sorry I'm not sure) rather then under current Windows Login account. So you can check the connection error message, is it something like this?

Login failed for 'XXXXXXX'

Where 'XXXXXX' stands for an account. Then check the logins in you SQL Server instace (it is '.\SQLExpress' in your application). Add the account to SQL logins and give it proper permission to access database objects; or you can add it to 'sysadmin' role so that it can access all database objects.

|||

Hi lori_Jay,

Thanks for your response,

I put <customErrors mode="Off"/> in my web.config and now at least I see the error. (see below on this post)

But I'm afraid you lost me with the part:

Then check the logins in you SQL Server instace (it is '.\SQLExpress' in your application). Add the account to SQL logins and give it proper permission to access database objects; or you can add it to 'sysadmin' role so that it can access all database objects.

Got no idea where in the application i can check the logins like you suggest. Let alone the rest of your advice.

Lex

CREATE DATABASE permission denied in database 'master'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.OleDb.OleDbException: CREATE DATABASE permission denied in database 'master'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[OleDbException (0x80004005): CREATE DATABASE permission denied in database 'master'.] System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) +1054817 System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) +53 System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) +27 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +47 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.OleDb.OleDbConnection.Open() +37 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +86 dotForumClientTopic.viewAllRecords.bindData() +169 dotForumClientTopic.viewAllRecords.Page_Load(Object sender, EventArgs e) +32 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +34 System.Web.UI.Control.OnLoad(EventArgs e) +99 System.Web.UI.Control.LoadRecursive() +47 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

|||

Sorry for misunderstanding^_^

Anyways we got closer to the cause. From the error message we can see the problem is the Login used in applcation connection did not have sufficient permission to CREATE DATABASE; whileCREATE DATABASE permission defaults to members of thesysadmin anddbcreator fixed server roles. So let's try to add the 'NT AUTHORITY\NETWORK SERVICE' (or 'ASPNET', something like these which used by IIS when connecting to SQL with Windows Authentication) to sysadmin role. You can do this simply in Management Studio->MyInstance->Security->Logins; or if you do not have Management Studio, just try to run this T-SQL command:

EXEC sp_addsrvrolemember 'NT AUTHORITY\NETWORK SERVICE' ,'sysadmin'

|||

Hi,

You just made me a happy man!

If you new how long this has been bugging me.

Adding Sysadmin role to <Servername>\ASPNET did the trick.

Now at last I can get on with all the other Forum problems.

Thanks again,

Lex

|||

Hi again,

Now the Forum is finally online I am trying to evaluate a number of things so I get the optimal result in learning from the problem you solved.

Is there a preferred way for Authentication.to SQLserver /express. Would it be better to go for SQL Server Authentication?

--------

I also had to addDatabase=dbname

Old:<addkey="forumDSN"value="Provider=SQLOLEDB; Server=.\SQLExpress;AttachDbFilename=|DataDirectory|dotForum2004.mdf;Trusted_Connection=Yes;" />

New: <

addkey="forumDSN"value="Provider=SQLOLEDB; Server=.\SQLExpress;AttachDbFilename=|DataDirectory|dotForum2004.mdf;Database=dbname;Trusted_Connection=Yes;Language=Dutch" />

Somehow during my various attempts (of trial and error) I must have created this database.

I tried to change that part of the key in Database=Forum

Locally a database with the name Forum appears in Management Studio, but I get an error when I try it on the webserver. I thought again this could be solved with security, so I checked.

All roles (including the ones you advised have dbcreator rights) Was this Database dbname then created manuelly at some time, or am i overlooking something else?

Thanks in advance,

Lex