SQL Server Express Security Issues

Nov 22, 2006

Hi,
I'm quite new to SQL Server Express and am having trouble with security. I have SQL Server Express installed on one of our servers and want to limit access to this instance of SQL Server, and basically grant access to only certain users within our organisation. However it seems that by default everyone can connect to SQL Server.

First of all I enabled local and remote connections. I did not grant anyone access permissions under Logins, and then went back to my workstation and tried to connect to the instance of SQL Server using SQL Server Management Studio which I have installed locally. I presumed and hoped that it would come up with a message saying that I don't have access permissions to connect to SQL Server, but instead it let me connect and even create a new database.

I then went back to the server and changed EACH Login to Deny under Login Properties -> Status -> Permission to connect to Database Engine. Then I returned to my client and tried to connect again and it still succeeds and allows me to create/delete tables/ databases/etc.

Can someone please explain why this is? If any more details are required to answer this question let me know.

Thanks!!

View 7 Replies

SQL Server Express Security?

Oct 21, 2005

We are releasing an application built on SQL Server Express in the next few months. Our clients are mostly small companies with little technical knowledge, and they will be using our application on desktop computers where everyone likely logs in as the same user. As such, my thinking is that we setup SQL Server Express as in SQL Server Authentication mode. This way they could select an Administrator password upon first installation, and manage user names and passwords from within the application itself.

View 22 Replies View Related

SQL Server Express Security Nightmare

Apr 16, 2007

I've built this prototype web app in asp.net 2.0

it uses a sql server express database.

It uses the database to populate a grid control.

It works when I run it from my pc when I

1). run it in debug mode.

2) right click a page in the vs2005 IDE and browse

3). browse a page from the IIS snap in

it doesn't work from other peoples PC on our intranet say if someone types:

http://xxx.xxx.xx.xx/mywebapp/default.aspx because I get this result:

Server Error in '/FocusII_Website' Application.
--------------------------------------------------------------------------------

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 82:
Line 83: SqlDataAdapter da = new SqlDataAdapter(sql, ConfigurationManager.ConnectionStrings["sqlExpress"].ToString());
Line 84: da.Fill(ds);
Line 85: RadGrid1.DataSource = ds;
Line 86: RadGrid1.DataBind();

Source File: c:InetpubwwwrootFocusII_WebsiteUnitGridPrototype.aspx.cs Line: 84

Stack Trace:

[SqlException (0x80131904): User does not have permission to perform this action.]
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +437
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
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
UnitGridPrototype.Page_Load(Object sender, EventArgs e) in c:InetpubwwwrootFocusII_WebsiteUnitGridPrototype.aspx.cs:84
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

Impersonation is set to TRUE

Anonymous access is checked.

Integrated Windows Authentication is checked..

aspnet, iusr accounts have full privs on the data directory,,,,,

I'm at a loss..