DB Connection details

Error with Database MS SQL Express 2008

  • (2 Pages)
  • +
  • 1
  • 2

16 Replies - 2811 Views - Last Post: 17 August 2009 - 06:47 AM

#1 Dave W  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 67
  • Joined: 18-October 08

DB Connection details

Post icon  Posted 31 July 2009 - 10:02 AM

I have a problem with my site that I am trying to setup on a server.

The site works because we have had it running before and the error we are getting is "User does not have permission to perform this action."

My connection String is
	<add name="ConStrinName" connectionString="data source=.\SQLEXPRESS;Integrated Security=True;AttachDBFilename=|DataDirectory|\DBNAME.mdf;User Instance=true" providerName="System.Data.SqlClient"/>



We have tried removing the User Instance and still not getting any joy. We have given the folder permissions for everyone to test this as well but it still does not work.

Anyone got any idea's please.

 User does not have permission to perform this action.
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.SqlClient.SqlException: User does not have permission to perform this action.

Source Error:

Line 3738:			Me.Adapter.SelectCommand = Me.CommandCollection(0)
Line 3739:			Dim dataTable As SalonDataSet.StaffDataTable = New SalonDataSet.StaffDataTable
Line 3740:			Me.Adapter.Fill(dataTable)
Line 3741:			Return dataTable
Line 3742:		End Function


Source File: C:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\1113f9f2\9307b5a1\App_Code.jlxg0aqn.2.vb	Line: 3740

Stack Trace:

[SqlException (0x80131904): User does not have permission to perform this action.]
   System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +428
   System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +65
   System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +117
   System.Data.SqlClient.SqlConnection.Open() +122
   System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) +31
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, Commandbehavior behavior) +112
   System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, Commandbehavior behavior) +162
   System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) +115
   SalonDataSetTableAdapters.StaffTableAdapter.GetStaff() in C:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\1113f9f2\9307b5a1\App_Code.jlxg0aqn.2.vb:3740

[TargetInvocationException: Exception has been thrown by the target of an invocation.]
   System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +0
   System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +71
   System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) +350
   System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +29
   System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method, Boolean disposeInstance, Object& instance) +488
   System.Web.UI.WebControls.ObjectDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1247
   System.Web.UI.WebControls.Repeater.GetData() +35
   System.Web.UI.WebControls.Repeater.CreateControlHierarchy(Boolean useDataSource) +220
   System.Web.UI.WebControls.Repeater.OnDataBinding(EventArgs e) +51
   System.Web.UI.WebControls.Repeater.DataBind() +75
   System.Web.UI.WebControls.Repeater.EnsureDataBound() +55
   System.Web.UI.WebControls.Repeater.OnPreRender(EventArgs e) +15
   System.Web.UI.Control.PreRenderRecursiveInternal() +80
   System.Web.UI.Control.PreRenderRecursiveInternal() +171
   System.Web.UI.Control.PreRenderRecursiveInternal() +171
   System.Web.UI.Control.PreRenderRecursiveInternal() +171
   System.Web.UI.Control.PreRenderRecursiveInternal() +171
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +842



We are running it on Windows 2008 with MS SQL Express 2008 Database

Thanks
Dave

This post has been edited by Dave W: 31 July 2009 - 10:17 AM


Is This A Good Question/Topic? 0
  • +

Replies To: DB Connection details

#2 woodjom  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 29
  • View blog
  • Posts: 549
  • Joined: 08-May 08

Re: DB Connection details

Posted 31 July 2009 - 11:24 AM

Do you have a SMS (Sql Management Studio) app installed? If i were you would try and setup a login account to the database and try it with a username and password.

I use SQL2k5 so not sure about Express aside from the fact that i have rarely been able to get it to work properly, such as looking at the database, tables, etc without using an Development Express console (VB, C#, Web) and even then it was so diluted that i almost felt tied up in my seat and did a Remove Program very fast.

Try downloading Oracles SQL-dev and see if you can attach to the file with that. Its not that great for a GUI to SQl but it worked the few times i actually was able to look at the SQL-E database. Key Note: hope you have experience with MySQL, as to do the good stuff you will need to do a cmd-line instruction on the db.

I think your problem is that the DB does not have anyone set as dbo (database-owner).
Was This Post Helpful? 0
  • +
  • -

#3 Dave W  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 67
  • Joined: 18-October 08

Re: DB Connection details

Posted 01 August 2009 - 06:00 AM

We have tried this but to no joy we are getting the sam error but we can now view it on our local machine which we could not do before.
Was This Post Helpful? 0
  • +
  • -

#4 woodjom  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 29
  • View blog
  • Posts: 549
  • Joined: 08-May 08

Re: DB Connection details

Posted 03 August 2009 - 06:03 AM

View PostDave W, on 1 Aug, 2009 - 07:00 AM, said:

We have tried this but to no joy we are getting the sam error but we can now view it on our local machine which we could not do before.


If you can view it locally....then your problem is the address to the database. When developing applications i use the DataSet/TableAdapter wizard, or an XSD file, to assist with database application help. This file will allow you to take a table, view, stored procedure or function and create a dataset & tabladapter based on the returned fields of the resulting query. Be careful though with this as if you use the * field accessor, then all fields from the resulting query will be setup. I never use * field accessor, unless im query testing (mostly difficult queries). Defining your parameters/fields for a query will help in reducing the query result speed and the processing of the DBMS.

Most likely it is working locally cause the connection string references "localhost" somewhere in the connection string. You will need to define the database location as either an <IP address>/<DB Instance> : <port> or the database server's IP address. There are alot of useable connection strings, i would look at the syntax of the connection string and suit it to your needs. Remember if your using this just on your machine, then local host is fine, but if you want others outside of your computer to use this connection string, you will need to explicitly type where the database is located so they can access it.
Was This Post Helpful? 0
  • +
  • -

#5 Dave W  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 67
  • Joined: 18-October 08

Re: DB Connection details

Posted 05 August 2009 - 10:21 AM

We have access to the server and can view the website with Visual Web Developer on it so we know it is working it is just getting it to be viewed with the Domain Name where the problem is
Was This Post Helpful? 0
  • +
  • -

#6 woodjom  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 29
  • View blog
  • Posts: 549
  • Joined: 08-May 08

Re: DB Connection details

Posted 05 August 2009 - 04:28 PM

View PostDave W, on 5 Aug, 2009 - 11:21 AM, said:

We have access to the server and can view the website with Visual Web Developer on it so we know it is working it is just getting it to be viewed with the Domain Name where the problem is



I wouldnt use VWD as a guage of if the settings are correct or not. VWD will use a webdev emulator to represent the website and wrap the site into a debugger instance. As such, it is great for developing but you need to post the site to a test location that can be rendered in a browser independent of VWD. Let me know how it turns out, i have run into this problem several times where the developement environment it worked but when deployed to an actual web address, it failed out and didnt work and would spend weeks figuring it out.
Was This Post Helpful? 0
  • +
  • -

#7 Dave W  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 67
  • Joined: 18-October 08

Re: DB Connection details

Posted 06 August 2009 - 12:40 AM

We have spent a couple of weeks trying to sort it out and now have an IT Training Company Tutor looking at it.
Was This Post Helpful? 0
  • +
  • -

#8 Dave W  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 67
  • Joined: 18-October 08

Re: DB Connection details

Posted 07 August 2009 - 01:59 AM

Anyone else got any idea's on what the problem could be, we are open to any series suggestions.

Thnaks
Dave
Was This Post Helpful? 0
  • +
  • -

#9 Dave W  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 67
  • Joined: 18-October 08

Re: DB Connection details

Posted 07 August 2009 - 04:38 AM

Just had this from someone who looked at the server and tried to sort out the issue

"Current security setup on the web server doesn’t seems to allow SQL attachment like the way you need."

So I have to get the security setup sorted.

thanks
Dave
Was This Post Helpful? 0
  • +
  • -

#10 woodjom  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 29
  • View blog
  • Posts: 549
  • Joined: 08-May 08

Re: DB Connection details

Posted 07 August 2009 - 06:12 PM

if thats the case then create a user and password to access the SQL server instance and then put it in the connection string.

for instance:
username: webdev
password: L3tm31nN0w
Was This Post Helpful? 0
  • +
  • -

#11 Dave W  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 67
  • Joined: 18-October 08

Re: DB Connection details

Posted 08 August 2009 - 04:47 AM

Quote

if thats the case then create a user and password to access the SQL server instance and then put it in the connection string.


if only it was that easy, we need it to be done so the database can be read straight from the App_Data Folder without any altering the Connection String; so that when a third party wants it we can say it is sorted already.

thanks
Dave
Was This Post Helpful? 0
  • +
  • -

#12 Dave W  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 67
  • Joined: 18-October 08

Re: DB Connection details

Posted 10 August 2009 - 12:45 AM

I have being told if I want this setup then I will need to "you need to build or add a custom script " if I want the app_data folder to automatically load database.

Can anyone confirm please.

Thanks
Was This Post Helpful? 0
  • +
  • -

#13 Dave W  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 67
  • Joined: 18-October 08

Re: DB Connection details

Posted 10 August 2009 - 08:38 AM

Managed to get the DB's Working at last, but is there a way for MS Sql Express 2008 to connect to the DB automatically when the DB is in the App_Data Folder or do I have to keep doing it manually?

Regards
Dave
Was This Post Helpful? 0
  • +
  • -

#14 woodjom  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 29
  • View blog
  • Posts: 549
  • Joined: 08-May 08

Re: DB Connection details

Posted 10 August 2009 - 09:29 AM

In my experience, the only time i have used the App_Data folder is with pseudo databases. Such as Excel (dont ask), flat files (txt extensions), and other various file type database schemas. As far as SQL Express....it operates very similar to SQL server, in that all you have to do is reference it by <machine name>/<instance> (by default: <machine name>/SQLEXPRESS) and then the database name will be in the connection string as "Initial Catalog".

As far as a script for the database, that is in reference to developing on SQL express and then taking the developed database and pushing it to a live server. I would strongly suggest against posting your MDF database file in the App_Data folder, unless you have mapped that SQL server to that directory to load the MDF. Best to keep a DBMS data store seperate from your web content for portability reasons.

"Just because you can, does not mean you should!" --Unknown

As i dont have alot of experience dealing with SQL Express on a production, or live, situation. i cannot help you. I deal with SQL Server standard edition, here at my job. But in the past i have used SQL Express at home to develop the database, test its functionality and then script export out the schema of the database and then load it into a MySQL DBMS for live usage for my sites.

Again, if you are hosting this, web code & database, on your home computer make sure you DO NOT reference the database server from "localhost". Otherwise your users will never make the connection. Remember the connection string will be relative to the user looking at the site. So if you say "localhost" in the connection string, their computer will be looking at their "localhost", which most likely will not have the database on their computer. As such, they will not be able to successfully connect to the database.
Was This Post Helpful? 1
  • +
  • -

#15 Dave W  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 67
  • Joined: 18-October 08

Re: DB Connection details

Posted 10 August 2009 - 10:39 AM

Got this sorted now thanks for all the help
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2