Hullo folks, I'm having what I assume is a fairly mundane security issue.
I have a SQL login that I am trying to restrict as much as possible. This account's sole goal in life is to hit the server, return some usage statistics, then truncate the table it received the statistics from. I would like to refrain from granting this login permissions on the physical target table if possible.
Usually I can wrap up "protected" operations in a stored procedure, then grant exec permissions for my user and I'm good to go. However, TRUNCATE TABLE gets cranky with me when I attempt the above method. I suspect that has to do with the fact that TRUNCATE TABLE is effectively modifying the table itself as opposed to merely deleting data.
Is it possible to grant this login ONLY execute permission on a stored proc that TRUNCATE's tables without giving the user any physical permissions? Am I going about this the wrong way?The only people who can use 'truncate' are the table owner, sysadmin, db_owner, and db_ddladmin; so if you have a user whose permissions are severely restricted I would not think 'truncate' would work, or if it would it would be a phaff to set up.
Why not just DELETE FROM, or use a temp table to create the statistics and return them?|||This is effectively what I ended up doing after making the same permissions determination. I had my heart set on Truncate do to the nature of the process itself, but I was able to work around the restriction using DELETE FROM and a local scheduled maintenance job.|||Is there a way he can call an osql command via xp_cmdshell that connects with a trusted connection that can truncate the table ? I tried some stuff with a server link to itself, but cannot truncate table link.db.owner.table, same goes with openquery (truncate not supported).|||That would present a larger security issue then outright granting the user required permissions. I definately don't want to expose trusted command line functionality to this login.
What I have now works, it's just not exactly the way I want it to. All of my research indicates that it will just have to be good enough as I cannot get around that pesky permissions requirement for TRUNCATE.|||In the past I have allowed a user to own a single table for a similar purpose. But that was for an import table, where the data was truncated, reloaded, diced up, and distributed to other tables. No other process even knew the table was there. It gets you out of having to have db_owner rights, but there is no other way around the requirement of owning the table.|||why dont you leave the table there and run a nightly process that truncates any table that had statistics grabbed from it
have an admin account run the nightly process, but the restricted user still gets their statistics
just a thought|||why dont you leave the table there and run a nightly process that truncates any table that had statistics grabbed from it
have an admin account run the nightly process, but the restricted user still gets their statistics
just a thought
That's the current working solution. The process runs a couple times a day, but same idea.
Showing posts with label login. Show all posts
Showing posts with label login. Show all posts
Friday, March 16, 2012
Thursday, March 8, 2012
"sa" Login locking resources?
I see lock timeouts in my trace and the object_id is for the same table
every time.
However, something does not seem right - the timeouts all show login = "sa".
Also, when I did a trace of lock acquired I see a lot of locks for that
table being acquired by login = "sa". There are locks being acquired by the
user being used for the connection by the application. But I cannot figure
out why "sa" is locking anything.
Can anyone out there help me?
TIA,
RohanDo you have any scheduled jobs that could be running in the context of 'sa'.
.?
"Rohan Hattangdi" wrote:
> I see lock timeouts in my trace and the object_id is for the same table
> every time.
> However, something does not seem right - the timeouts all show login = "sa
".
> Also, when I did a trace of lock acquired I see a lot of locks for that
> table being acquired by login = "sa". There are locks being acquired by th
e
> user being used for the connection by the application. But I cannot figure
> out why "sa" is locking anything.
> Can anyone out there help me?
> TIA,
> Rohan
>
>|||No ...
This is a load-test server and the only three jobs on there are for backup,
integrity checks and optimizations - they run at 12am, 1am and 2am
respectively. So it could not be jobs.
Any other ideas?
TIA,
Rohan
"Alien2_51" <dan.billow.remove@.monacocoach.removeme.com> wrote in message
news:E4FED000-D1E9-4D15-8118-8E751AA94CD9@.microsoft.com...
> Do you have any scheduled jobs that could be running in the context of
> 'sa'...?
> "Rohan Hattangdi" wrote:
>|||Rohan Hattangdi wrote:
> No ...
> This is a load-test server and the only three jobs on there are for
> backup, integrity checks and optimizations - they run at 12am, 1am
> and 2am respectively. So it could not be jobs.
> Any other ideas?
> TIA,
> Rohan
>
Change the "sa" password and see if the problem continues. If someone is
using the "sa" account without you knowing about it or an application
somewhere hard-codes the account login, you're likely to get a phone
call.
The other thing you can do is check the net_address in the sysprocesses
table and call your network admin and have him/her tell you whose PC the
MAC address belongs to.
David Gugick
Imceda Software
www.imceda.com|||That is a good idea.
Let me see what happens now.
Thank you,
Rohan
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OUDf6ylKFHA.1284@.TK2MSFTNGP14.phx.gbl...
> Rohan Hattangdi wrote:
> Change the "sa" password and see if the problem continues. If someone is
> using the "sa" account without you knowing about it or an application
> somewhere hard-codes the account login, you're likely to get a phone call.
> The other thing you can do is check the net_address in the sysprocesses
> table and call your network admin and have him/her tell you whose PC the
> MAC address belongs to.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||I spoke too soon.
Changing the pwd seems to have had no effect.
This is very strange. Why would it say "sa"? If a lock timeout event
occurs on the connection made by the application, should not the login name
be that used by the application to connect to SQL Server?
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OUDf6ylKFHA.1284@.TK2MSFTNGP14.phx.gbl...
> Rohan Hattangdi wrote:
> Change the "sa" password and see if the problem continues. If someone is
> using the "sa" account without you knowing about it or an application
> somewhere hard-codes the account login, you're likely to get a phone call.
> The other thing you can do is check the net_address in the sysprocesses
> table and call your network admin and have him/her tell you whose PC the
> MAC address belongs to.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Rohan Hattangdi wrote:
> I spoke too soon.
> Changing the pwd seems to have had no effect.
> This is very strange. Why would it say "sa"? If a lock timeout event
> occurs on the connection made by the application, should not the
> login name be that used by the application to connect to SQL Server?
Were those "sa" logins logged out before the password change? If not, I
think they woudl stay connected and would just have a problem the next
time around. But, yes, in most cases you should see the login name used
in the lock timeout event. Unless you are using Application Roles.
Run sp_helprole and see if any are Application Roles.
David Gugick
Imceda Software
www.imceda.com
every time.
However, something does not seem right - the timeouts all show login = "sa".
Also, when I did a trace of lock acquired I see a lot of locks for that
table being acquired by login = "sa". There are locks being acquired by the
user being used for the connection by the application. But I cannot figure
out why "sa" is locking anything.
Can anyone out there help me?
TIA,
RohanDo you have any scheduled jobs that could be running in the context of 'sa'.
.?
"Rohan Hattangdi" wrote:
> I see lock timeouts in my trace and the object_id is for the same table
> every time.
> However, something does not seem right - the timeouts all show login = "sa
".
> Also, when I did a trace of lock acquired I see a lot of locks for that
> table being acquired by login = "sa". There are locks being acquired by th
e
> user being used for the connection by the application. But I cannot figure
> out why "sa" is locking anything.
> Can anyone out there help me?
> TIA,
> Rohan
>
>|||No ...
This is a load-test server and the only three jobs on there are for backup,
integrity checks and optimizations - they run at 12am, 1am and 2am
respectively. So it could not be jobs.
Any other ideas?
TIA,
Rohan
"Alien2_51" <dan.billow.remove@.monacocoach.removeme.com> wrote in message
news:E4FED000-D1E9-4D15-8118-8E751AA94CD9@.microsoft.com...
> Do you have any scheduled jobs that could be running in the context of
> 'sa'...?
> "Rohan Hattangdi" wrote:
>|||Rohan Hattangdi wrote:
> No ...
> This is a load-test server and the only three jobs on there are for
> backup, integrity checks and optimizations - they run at 12am, 1am
> and 2am respectively. So it could not be jobs.
> Any other ideas?
> TIA,
> Rohan
>
Change the "sa" password and see if the problem continues. If someone is
using the "sa" account without you knowing about it or an application
somewhere hard-codes the account login, you're likely to get a phone
call.
The other thing you can do is check the net_address in the sysprocesses
table and call your network admin and have him/her tell you whose PC the
MAC address belongs to.
David Gugick
Imceda Software
www.imceda.com|||That is a good idea.
Let me see what happens now.
Thank you,
Rohan
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OUDf6ylKFHA.1284@.TK2MSFTNGP14.phx.gbl...
> Rohan Hattangdi wrote:
> Change the "sa" password and see if the problem continues. If someone is
> using the "sa" account without you knowing about it or an application
> somewhere hard-codes the account login, you're likely to get a phone call.
> The other thing you can do is check the net_address in the sysprocesses
> table and call your network admin and have him/her tell you whose PC the
> MAC address belongs to.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||I spoke too soon.
Changing the pwd seems to have had no effect.
This is very strange. Why would it say "sa"? If a lock timeout event
occurs on the connection made by the application, should not the login name
be that used by the application to connect to SQL Server?
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OUDf6ylKFHA.1284@.TK2MSFTNGP14.phx.gbl...
> Rohan Hattangdi wrote:
> Change the "sa" password and see if the problem continues. If someone is
> using the "sa" account without you knowing about it or an application
> somewhere hard-codes the account login, you're likely to get a phone call.
> The other thing you can do is check the net_address in the sysprocesses
> table and call your network admin and have him/her tell you whose PC the
> MAC address belongs to.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Rohan Hattangdi wrote:
> I spoke too soon.
> Changing the pwd seems to have had no effect.
> This is very strange. Why would it say "sa"? If a lock timeout event
> occurs on the connection made by the application, should not the
> login name be that used by the application to connect to SQL Server?
Were those "sa" logins logged out before the password change? If not, I
think they woudl stay connected and would just have a problem the next
time around. But, yes, in most cases you should see the login name used
in the lock timeout event. Unless you are using Application Roles.
Run sp_helprole and see if any are Application Roles.
David Gugick
Imceda Software
www.imceda.com
Tuesday, March 6, 2012
"NT Authority/NetworkService" not available as a login
Just installed Server 2003 and then SQL server 2000 followed by sp3a and I'm trying to use an ASP.NET program called DNN which wants "NT Authority / NetworkService" user for access to the database. However, "NT Authority / NetworkService" is not showing
up in the database after installation.
Why is this?
How can I fix it?
Thanks
OK...finally, out of frustration, I just pasted in the name 'NT Authority/NetWorkService'
Start at:
SQL Server Enterprise Manager,
Security node,
Logins node (right click and select New Login),
SQL Server Login properties - New Login,
Paste 'NT Authority/NetWorkService' into the 'name field'
The 'NT Authority/NetWorkService' user was created and it also appears to work properly.
|||You did the correct thing, and Dot Net Nuke should now be happy.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"oloooooo" <anonymous@.discussions.microsoft.com> wrote in message
news:531707DF-C146-4ED0-A845-978CECF9845C@.microsoft.com...
> Just installed Server 2003 and then SQL server 2000 followed by sp3a and
I'm trying to use an ASP.NET program called DNN which wants "NT Authority /
NetworkService" user for access to the database. However, "NT Authority /
NetworkService" is not showing up in the database after installation.
> Why is this?
> How can I fix it?
> Thanks
>
up in the database after installation.
Why is this?
How can I fix it?
Thanks
OK...finally, out of frustration, I just pasted in the name 'NT Authority/NetWorkService'
Start at:
SQL Server Enterprise Manager,
Security node,
Logins node (right click and select New Login),
SQL Server Login properties - New Login,
Paste 'NT Authority/NetWorkService' into the 'name field'
The 'NT Authority/NetWorkService' user was created and it also appears to work properly.
|||You did the correct thing, and Dot Net Nuke should now be happy.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"oloooooo" <anonymous@.discussions.microsoft.com> wrote in message
news:531707DF-C146-4ED0-A845-978CECF9845C@.microsoft.com...
> Just installed Server 2003 and then SQL server 2000 followed by sp3a and
I'm trying to use an ASP.NET program called DNN which wants "NT Authority /
NetworkService" user for access to the database. However, "NT Authority /
NetworkService" is not showing up in the database after installation.
> Why is this?
> How can I fix it?
> Thanks
>
"NT Authority/NetworkService" not available as a login
Just installed Server 2003 and then SQL server 2000 followed by sp3a and I'm
trying to use an ASP.NET program called DNN which wants "NT Authority / Net
workService" user for access to the database. However, "NT Authority / Netw
orkService" is not showing
up in the database after installation.
Why is this?
How can I fix it?
ThanksOK...finally, out of frustration, I just pasted in the name 'NT Authority/Ne
tWorkService'
Start at:
SQL Server Enterprise Manager,
Security node,
Logins node (right click and select New Login),
SQL Server Login properties - New Login,
Paste 'NT Authority/NetWorkService' into the 'name field'
The 'NT Authority/NetWorkService' user was created and it also appears to wo
rk properly.|||You did the correct thing, and Dot Net Nuke should now be happy.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"oloooooo" <anonymous@.discussions.microsoft.com> wrote in message
news:531707DF-C146-4ED0-A845-978CECF9845C@.microsoft.com...
> Just installed Server 2003 and then SQL server 2000 followed by sp3a and
I'm trying to use an ASP.NET program called DNN which wants "NT Authority /
NetworkService" user for access to the database. However, "NT Authority /
NetworkService" is not showing up in the database after installation.
> Why is this?
> How can I fix it?
> Thanks
>
trying to use an ASP.NET program called DNN which wants "NT Authority / Net
workService" user for access to the database. However, "NT Authority / Netw
orkService" is not showing
up in the database after installation.
Why is this?
How can I fix it?
ThanksOK...finally, out of frustration, I just pasted in the name 'NT Authority/Ne
tWorkService'
Start at:
SQL Server Enterprise Manager,
Security node,
Logins node (right click and select New Login),
SQL Server Login properties - New Login,
Paste 'NT Authority/NetWorkService' into the 'name field'
The 'NT Authority/NetWorkService' user was created and it also appears to wo
rk properly.|||You did the correct thing, and Dot Net Nuke should now be happy.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"oloooooo" <anonymous@.discussions.microsoft.com> wrote in message
news:531707DF-C146-4ED0-A845-978CECF9845C@.microsoft.com...
> Just installed Server 2003 and then SQL server 2000 followed by sp3a and
I'm trying to use an ASP.NET program called DNN which wants "NT Authority /
NetworkService" user for access to the database. However, "NT Authority /
NetworkService" is not showing up in the database after installation.
> Why is this?
> How can I fix it?
> Thanks
>
"NT Authority/NetworkService" not available as a login
Just installed Server 2003 and then SQL server 2000 followed by sp3a and I'm trying to use an ASP.NET program called DNN which wants "NT Authority / NetworkService" user for access to the database. However, "NT Authority / NetworkService" is not showing up in the database after installation
Why is this
How can I fix it
ThankOK...finally, out of frustration, I just pasted in the name 'NT Authority/NetWorkService
Start at
SQL Server Enterprise Manager
Security node
Logins node (right click and select New Login)
SQL Server Login properties - New Login
Paste 'NT Authority/NetWorkService' into the 'name field
The 'NT Authority/NetWorkService' user was created and it also appears to work properly.|||You did the correct thing, and Dot Net Nuke should now be happy.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"oloooooo" <anonymous@.discussions.microsoft.com> wrote in message
news:531707DF-C146-4ED0-A845-978CECF9845C@.microsoft.com...
> Just installed Server 2003 and then SQL server 2000 followed by sp3a and
I'm trying to use an ASP.NET program called DNN which wants "NT Authority /
NetworkService" user for access to the database. However, "NT Authority /
NetworkService" is not showing up in the database after installation.
> Why is this?
> How can I fix it?
> Thanks
>
Why is this
How can I fix it
ThankOK...finally, out of frustration, I just pasted in the name 'NT Authority/NetWorkService
Start at
SQL Server Enterprise Manager
Security node
Logins node (right click and select New Login)
SQL Server Login properties - New Login
Paste 'NT Authority/NetWorkService' into the 'name field
The 'NT Authority/NetWorkService' user was created and it also appears to work properly.|||You did the correct thing, and Dot Net Nuke should now be happy.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"oloooooo" <anonymous@.discussions.microsoft.com> wrote in message
news:531707DF-C146-4ED0-A845-978CECF9845C@.microsoft.com...
> Just installed Server 2003 and then SQL server 2000 followed by sp3a and
I'm trying to use an ASP.NET program called DNN which wants "NT Authority /
NetworkService" user for access to the database. However, "NT Authority /
NetworkService" is not showing up in the database after installation.
> Why is this?
> How can I fix it?
> Thanks
>
Saturday, February 25, 2012
"Login Failed" for SQL Express
Whenever I try to connect to my SQL Express Database with C# code,(.net 2.0), it says "Login failed for user 'TUser'. The user is not associated with a trusted SQL Server connection."
But I can connect with SQL Express Manager.
I've heard somewhere this is a SQL authentication problem, I didn't enable it during installation.
So is there a way to fix this? How can I enable SQL authentication without uninstalling it?
Is there anyone to help me?"Login failed for user" on external webhosting
I get the following error message when trying to access SQL server database hosted by Godaddy.com. I've enabled ASP.net 2.0 and setup SQL 2005 schema, followed their directions for the connectionstring and am using the SQL account and password I setup on Godaddy's website to access the SQL database. When I attempt to use the built in create user or login form that I created from visual studio express that works while using sql 2005 express on my localmachine, it gives the following error message:
Login failed for user 'ysatrifecta'.
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: Login failed for user 'ysatrifecta'.
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:
[SqlException (0x80131904): Login failed for user 'ysatrifecta'.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734931
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
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.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +84
System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +197
System.Web.Security.SqlMembershipProvider.CreateUser(String username, String password, String email, String passwordQuestion, String passwordAnswer, Boolean isApproved, Object providerUserKey, MembershipCreateStatus& status) +3612
System.Web.UI.WebControls.CreateUserWizard.AttemptCreateUser() +305
System.Web.UI.WebControls.CreateUserWizard.OnNextButtonClick(WizardNavigationEventArgs e) +105
System.Web.UI.WebControls.Wizard.OnBubbleEvent(Object source, EventArgs e) +453
System.Web.UI.WebControls.CreateUserWizard.OnBubbleEvent(Object source, EventArgs e) +149
System.Web.UI.WebControls.WizardChildTable.OnBubbleEvent(Object source, EventArgs args) +17
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Can anyone help?
I had misspelled the hostname in the connectionstring...doh!"Login failed for user" error
hi ppl,
I'm having trouble connecting to my database using ASP.NET
I have been getting the error below whenever i try open the connection using connection.open using vb.net
I have this error in when using plain text editor as and even using
the visual studio.nets data tools (sqlconnection,sqldataadapter tool.)
The strange thing is that i can easily preview the data in any table in northwind db using the connections i've created in the solution explorer using visual studio.net.
This is the connection string created by visual studio.net (i also tried to use this connectionstring manually in code)
"initial catalog=Northwind;integrated security=SSPI;persist security info=False;workstation id=COMPI;packet size=4096"
Here is the error (same error for plain text and vs.net):
Login failed for user 'COMPI\ASPNET'
...
Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'COMPI\ASPNET'.
...
[SqlException: Login failed for user 'COMPI\ASPNET'.]
System.Data.SqlClient.ConnectionPool.GetConnection (Boolean& isInTransaction)
System.Data.SqlClient.SqlConnectionPoolManager.Get PooledConnection(SqlConnectionString options, Boolean& isInTransaction)
System.Data.SqlClient.SqlConnection.Open()
BegCh12.WebForm1.Page_Load(Object sender, EventArgs e) in C:\BegASPNet\BegCh12\WebForm1.aspx.vb:153
System.Web.UI.Control.OnLoad(EventArgs e)
System.Web.UI.Control.LoadRecursive()
System.Web.UI.Page.ProcessRequestMain()...
can someone help pls
Im using sql server 7 sp3, windows xp pro.I may have miss read this so let me restate your problem.
You have an application thaat can view and access data in the NorthWind DB but not the DB your app is written for. If that is true then it sounds like your userid "ASPNET" needs to be granted access to the db in question as well as execute or Select/Delete/Update permissions of all needed objects.|||How do i do what ur saying, is it from SQL server or using Windows XP.
I have just installed sql server and havent done any changes to permission or account (not sure how)?|||Use Enterprise Manager
Open your server, expand Security and then click on Users. right click on your UserID and select properties. Under the Database Access tab click on the Permit check box next to your database. Under the Permit in Database Role, you will want to select public, and probably db_datareader/datawrite OR you can open things up a bit and select db_owner, but I wouldn't use it for very long.
Books Online covers this very well.
I'm having trouble connecting to my database using ASP.NET
I have been getting the error below whenever i try open the connection using connection.open using vb.net
I have this error in when using plain text editor as and even using
the visual studio.nets data tools (sqlconnection,sqldataadapter tool.)
The strange thing is that i can easily preview the data in any table in northwind db using the connections i've created in the solution explorer using visual studio.net.
This is the connection string created by visual studio.net (i also tried to use this connectionstring manually in code)
"initial catalog=Northwind;integrated security=SSPI;persist security info=False;workstation id=COMPI;packet size=4096"
Here is the error (same error for plain text and vs.net):
Login failed for user 'COMPI\ASPNET'
...
Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'COMPI\ASPNET'.
...
[SqlException: Login failed for user 'COMPI\ASPNET'.]
System.Data.SqlClient.ConnectionPool.GetConnection (Boolean& isInTransaction)
System.Data.SqlClient.SqlConnectionPoolManager.Get PooledConnection(SqlConnectionString options, Boolean& isInTransaction)
System.Data.SqlClient.SqlConnection.Open()
BegCh12.WebForm1.Page_Load(Object sender, EventArgs e) in C:\BegASPNet\BegCh12\WebForm1.aspx.vb:153
System.Web.UI.Control.OnLoad(EventArgs e)
System.Web.UI.Control.LoadRecursive()
System.Web.UI.Page.ProcessRequestMain()...
can someone help pls
Im using sql server 7 sp3, windows xp pro.I may have miss read this so let me restate your problem.
You have an application thaat can view and access data in the NorthWind DB but not the DB your app is written for. If that is true then it sounds like your userid "ASPNET" needs to be granted access to the db in question as well as execute or Select/Delete/Update permissions of all needed objects.|||How do i do what ur saying, is it from SQL server or using Windows XP.
I have just installed sql server and havent done any changes to permission or account (not sure how)?|||Use Enterprise Manager
Open your server, expand Security and then click on Users. right click on your UserID and select properties. Under the Database Access tab click on the Permit check box next to your database. Under the Permit in Database Role, you will want to select public, and probably db_datareader/datawrite OR you can open things up a bit and select db_owner, but I wouldn't use it for very long.
Books Online covers this very well.
"Login failed for user" error
Hi
I have installed sql server on my standalone PC.
I used "window and sql mix authentication" for the sql server in the enterprise manager set up.
when I try to use the sql server login name for the startup service account, I have this error. "No Mapping between names and security IDs ".
So Even though I use mix authentication, I am still using window login name.
Then I create the DSN file.
But when I run the ASP program, I have this error "login fail for user-("user name that I used for dsn file configuration").
I try to configure the dsn file set up with both authentications, windows and sql login names.
But I got the error again.
I am really lost here.
Please help me. thanks...What's your connection string?|||when I try to use the sql server login name for the startup service account, I have this error. "No Mapping between names and security IDs ".
You cannot use a sql server login for the service accounts, these need to be windows accounts (domain or local user, or system account). The mixed authentication only applies to logins to the server.
I have installed sql server on my standalone PC.
I used "window and sql mix authentication" for the sql server in the enterprise manager set up.
when I try to use the sql server login name for the startup service account, I have this error. "No Mapping between names and security IDs ".
So Even though I use mix authentication, I am still using window login name.
Then I create the DSN file.
But when I run the ASP program, I have this error "login fail for user-("user name that I used for dsn file configuration").
I try to configure the dsn file set up with both authentications, windows and sql login names.
But I got the error again.
I am really lost here.
Please help me. thanks...What's your connection string?|||when I try to use the sql server login name for the startup service account, I have this error. "No Mapping between names and security IDs ".
You cannot use a sql server login for the service accounts, these need to be windows accounts (domain or local user, or system account). The mixed authentication only applies to logins to the server.
"Login failed for user MyPC/ASPNET"
Hi
I created my DB in SQL 2005 Express in VS IDE. I have my app in IIS. When I try to use the DB (e.g. try to login a user in the app) it gives me the following error:
Cannot open database "cvRepository" requested by the login. The login failed.
Login failed for user 'MyPC\ASPNET'.
My guess was that I had to give permissions to ASPNET user so I did in SQL Server Management. Still not working! Checked my Connection string and I think it's fine...
connectionString="Data Source=.\SQLEXPRESS;Database=cvRepository;Integrated Security=True;User Instance=True"Still not working!
Can anyone explain me what's wrong and to solve this?!
Try replacing Database=cvRepository withAttachDBFileName=|DataDirectory|cvRepository.mdfin your connectionString.
Hope this helps.
Mike
|||Do you have a database namedcvRepositoryon your SQL server? Or you're using a database file attached at runtime? Anyways you have to ensure the ASPNET account has login permission to the SQL server, as well as access permissions on the required database.|||Hi
Tried using the AttachDBFileName but nothing:
<
addname="cvRepositoryConnectionString"connectionString="Data Source=.\SQLEXPRESS;AttachDBFileName=|DataDirectory|cvrepository.mdf;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient" />I don't understand much of this but I'll shoot out something... When running the app in debug mode (http://localhost:2431/cvRepository/Default.aspx) it creates an instance of the SQL Server but when not doing so (http://localhost/cvRepository/Default.aspx) it doesn't, that's why I can't access the DB. Is this correct? If so, how to solve it?Any explanation/article would be helpful!
Thx
|||Try to add the ASPNET login to a member ofsysadminordbcreatorserver role.|||Hi
Instead of using User Instances I defined a particular user to access the DB. Used this:
connectionString
="Server=.\SQLEXPRESS;Database=cvrepository;User ID=user1;Password=user1pass"Thx for your help
Cheers
Sunday, February 19, 2012
"hashed" password on alter login command
Hi,
I need to synchronise logins between 2 SQL2005 instances on a regular basis
- while you can copy the passwords to the destination using the "HASHED"
keyword on the CREATE LOGIN command, it appears that "HASHED" is missing fro
m
the ALTER LOGIN command. This means that I can't just update the password
when a user on the source instance changes the password on their SQL
authenticated account.
I also looked at using the transfer logins task in SSIS, but then read the
following in book online:
"At the destination, the transferred logins are disabled and assigned random
passwords. A member of the sysadmin role on the destination server must
change the passwords and enable the logins before the logins can be used. "
Apart from recording the login's roles, database access, and permissions,
dropping the login, creating with the hashed password, and re-applying the
roles/database access/permissions - has anybody found a way to just apply a
new hashed password to the login?
AndyHello Andy,
Thank you for posting in MSDN newsgroup!
I understand that you'd like to transfer logins and passwords between SQL
2005 instances. You just want to apply a new hashed password to the logins.
If I'm off-base, please let me know.
I think there is no simple method to apply a new hased password as you
want. You need to drop/recreate the logins by using script and grant it the
proper permission. You have to know the original password so that you could
change it to the new password by using "Alter login" statement.
You may want to refer to the following articles for more details:
How to transfer the logins and the passwords between instances of SQL
Server 2005
http://support.microsoft.com/kb/918992/
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
(246133)
http://support.microsoft.com/defaul...KB;EN-US;246133
Your feedback on this feature is routed to the proper channel. In the
meantime, I also encourage you submit via the link below
http://lab.msdn.microsoft.com/produ...ck/default.aspx
If anything is unclear or you have further questions on the issue, please
feel free to let's know.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
I need to synchronise logins between 2 SQL2005 instances on a regular basis
- while you can copy the passwords to the destination using the "HASHED"
keyword on the CREATE LOGIN command, it appears that "HASHED" is missing fro
m
the ALTER LOGIN command. This means that I can't just update the password
when a user on the source instance changes the password on their SQL
authenticated account.
I also looked at using the transfer logins task in SSIS, but then read the
following in book online:
"At the destination, the transferred logins are disabled and assigned random
passwords. A member of the sysadmin role on the destination server must
change the passwords and enable the logins before the logins can be used. "
Apart from recording the login's roles, database access, and permissions,
dropping the login, creating with the hashed password, and re-applying the
roles/database access/permissions - has anybody found a way to just apply a
new hashed password to the login?
AndyHello Andy,
Thank you for posting in MSDN newsgroup!
I understand that you'd like to transfer logins and passwords between SQL
2005 instances. You just want to apply a new hashed password to the logins.
If I'm off-base, please let me know.
I think there is no simple method to apply a new hased password as you
want. You need to drop/recreate the logins by using script and grant it the
proper permission. You have to know the original password so that you could
change it to the new password by using "Alter login" statement.
You may want to refer to the following articles for more details:
How to transfer the logins and the passwords between instances of SQL
Server 2005
http://support.microsoft.com/kb/918992/
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
(246133)
http://support.microsoft.com/defaul...KB;EN-US;246133
Your feedback on this feature is routed to the proper channel. In the
meantime, I also encourage you submit via the link below
http://lab.msdn.microsoft.com/produ...ck/default.aspx
If anything is unclear or you have further questions on the issue, please
feel free to let's know.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Thursday, February 16, 2012
"exclusive access could not be obtained.." while restoring
Hello
I am using MSDE with my application and providing our users UI to backup/restore the database. My app has just 1 database and 1 login mapped to 1 user (MyAppUser). Backup and restore functionality is using inline sql commands. Backup works fine with something like this
Private Sub Backup(
Dim cn As New SqlConnection(MyConnectionString
Tr
cn.Open(
Dim cm As New SqlComman
With c
.Connection = c
.CommandType = CommandType.Tex
.CommandText = "BACKUP DATABASE MyDB TO DISK = 'D:\Backup\a.bak' WITH INIT
.ExecuteNonQuery(
End Wit
MsgBox("Database backed up successfully!"
Catch ex As Exceptio
MsgBox(ex.Message
Finall
cn.Close(
End Tr
End Su
but when I do restore using something like this
Private Sub Restore(
Dim cn As New SqlConnection(MyConnectionString
Tr
cn.Open(
Dim cm As New SqlComman
With c
.Connection = c
.CommandType = CommandType.Tex
.CommandText = "RESTORE DATABASE MyDB FROM DISK = 'D:\Backup\a.bak' WITH RECOVERY
.ExecuteNonQuery(
End Wit
MsgBox("Database restored successfully!"
Catch ex As Exceptio
MsgBox(ex.Message
Finall
cn.Close(
End Tr
End Su
I get the "exclusive access could not be obtained.. .. database is in use" error message
I did "Use Master" in query analyser and ran the same restore sql command and it worked fine. I do not know how to use "Use master" here in ado.net. I know it has something to do with sp_Who but not sure how the syntax will fit in.
Note MyConnectionString is something like
"data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ; Password = MyPassword"
Please help. What should I do so that this works in my vb.net app.You can use cn.ChangeDatabase("master") to change database
If you are restoring over an existing database you will also need specify
WITH REPLACE.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:C800D8D0-0D15-4352-8545-9CA47C9B4710@.microsoft.com...
> Hello,
> I am using MSDE with my application and providing our users UI to
backup/restore the database. My app has just 1 database and 1 login mapped
to 1 user (MyAppUser). Backup and restore functionality is using inline sql
commands. Backup works fine with something like this:
> Private Sub Backup()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "BACKUP DATABASE MyDB TO DISK ='D:\Backup\a.bak' WITH INIT"
> .ExecuteNonQuery()
> End With
> MsgBox("Database backed up successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> but when I do restore using something like this:
> Private Sub Restore()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "RESTORE DATABASE MyDB FROM DISK ='D:\Backup\a.bak' WITH RECOVERY"
> .ExecuteNonQuery()
> End With
> MsgBox("Database restored successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> I get the "exclusive access could not be obtained.. .. database is in use"
error message.
> I did "Use Master" in query analyser and ran the same restore sql command
and it worked fine. I do not know how to use "Use master" here in ado.net.
I know it has something to do with sp_Who but not sure how the syntax will
fit in.
> Note MyConnectionString is something like:
> "data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ;
Password = MyPassword"
> Please help. What should I do so that this works in my vb.net app.
I am using MSDE with my application and providing our users UI to backup/restore the database. My app has just 1 database and 1 login mapped to 1 user (MyAppUser). Backup and restore functionality is using inline sql commands. Backup works fine with something like this
Private Sub Backup(
Dim cn As New SqlConnection(MyConnectionString
Tr
cn.Open(
Dim cm As New SqlComman
With c
.Connection = c
.CommandType = CommandType.Tex
.CommandText = "BACKUP DATABASE MyDB TO DISK = 'D:\Backup\a.bak' WITH INIT
.ExecuteNonQuery(
End Wit
MsgBox("Database backed up successfully!"
Catch ex As Exceptio
MsgBox(ex.Message
Finall
cn.Close(
End Tr
End Su
but when I do restore using something like this
Private Sub Restore(
Dim cn As New SqlConnection(MyConnectionString
Tr
cn.Open(
Dim cm As New SqlComman
With c
.Connection = c
.CommandType = CommandType.Tex
.CommandText = "RESTORE DATABASE MyDB FROM DISK = 'D:\Backup\a.bak' WITH RECOVERY
.ExecuteNonQuery(
End Wit
MsgBox("Database restored successfully!"
Catch ex As Exceptio
MsgBox(ex.Message
Finall
cn.Close(
End Tr
End Su
I get the "exclusive access could not be obtained.. .. database is in use" error message
I did "Use Master" in query analyser and ran the same restore sql command and it worked fine. I do not know how to use "Use master" here in ado.net. I know it has something to do with sp_Who but not sure how the syntax will fit in.
Note MyConnectionString is something like
"data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ; Password = MyPassword"
Please help. What should I do so that this works in my vb.net app.You can use cn.ChangeDatabase("master") to change database
If you are restoring over an existing database you will also need specify
WITH REPLACE.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:C800D8D0-0D15-4352-8545-9CA47C9B4710@.microsoft.com...
> Hello,
> I am using MSDE with my application and providing our users UI to
backup/restore the database. My app has just 1 database and 1 login mapped
to 1 user (MyAppUser). Backup and restore functionality is using inline sql
commands. Backup works fine with something like this:
> Private Sub Backup()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "BACKUP DATABASE MyDB TO DISK ='D:\Backup\a.bak' WITH INIT"
> .ExecuteNonQuery()
> End With
> MsgBox("Database backed up successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> but when I do restore using something like this:
> Private Sub Restore()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "RESTORE DATABASE MyDB FROM DISK ='D:\Backup\a.bak' WITH RECOVERY"
> .ExecuteNonQuery()
> End With
> MsgBox("Database restored successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> I get the "exclusive access could not be obtained.. .. database is in use"
error message.
> I did "Use Master" in query analyser and ran the same restore sql command
and it worked fine. I do not know how to use "Use master" here in ado.net.
I know it has something to do with sp_Who but not sure how the syntax will
fit in.
> Note MyConnectionString is something like:
> "data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ;
Password = MyPassword"
> Please help. What should I do so that this works in my vb.net app.
Monday, February 13, 2012
"exclusive access could not be obtained.." while restoring
Hello,
I am using MSDE with my application and providing our users UI to backup/res
tore the database. My app has just 1 database and 1 login mapped to 1 user
(MyAppUser). Backup and restore functionality is using inline sql commands.
Backup works fine with so
mething like this:
Private Sub Backup()
Dim cn As New SqlConnection(MyConnectionString)
Try
cn.Open()
Dim cm As New SqlCommand
With cm
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "BACKUP DATABASE MyDB TO DISK = 'D:\Backup\a.bak' WITH INIT"
.ExecuteNonQuery()
End With
MsgBox("Database backed up successfully!")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cn.Close()
End Try
End Sub
but when I do restore using something like this:
Private Sub Restore()
Dim cn As New SqlConnection(MyConnectionString)
Try
cn.Open()
Dim cm As New SqlCommand
With cm
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "RESTORE DATABASE MyDB FROM DISK = 'D:\Backup\a.bak' WITH RE
COVERY"
.ExecuteNonQuery()
End With
MsgBox("Database restored successfully!")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cn.Close()
End Try
End Sub
I get the "exclusive access could not be obtained.. .. database is in use" e
rror message.
I did "Use Master" in query analyser and ran the same restore sql command an
d it worked fine. I do not know how to use "Use master" here in ado.net. I
know it has something to do with sp_Who but not sure how the syntax will fi
t in.
Note MyConnectionString is something like:
"data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ; Pa
ssword = MyPassword"
Please help. What should I do so that this works in my vb.net app.You can use cn.ChangeDatabase("master") to change database
If you are restoring over an existing database you will also need specify
WITH REPLACE.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:C800D8D0-0D15-4352-8545-9CA47C9B4710@.microsoft.com...
> Hello,
> I am using MSDE with my application and providing our users UI to
backup/restore the database. My app has just 1 database and 1 login mapped
to 1 user (MyAppUser). Backup and restore functionality is using inline sql
commands. Backup works fine with something like this:
> Private Sub Backup()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "BACKUP DATABASE MyDB TO DISK =
'D:\Backup\a.bak' WITH INIT"
> .ExecuteNonQuery()
> End With
> MsgBox("Database backed up successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> but when I do restore using something like this:
> Private Sub Restore()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "RESTORE DATABASE MyDB FROM DISK =
'D:\Backup\a.bak' WITH RECOVERY"
> .ExecuteNonQuery()
> End With
> MsgBox("Database restored successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> I get the "exclusive access could not be obtained.. .. database is in use"
error message.
> I did "Use Master" in query analyser and ran the same restore sql command
and it worked fine. I do not know how to use "Use master" here in ado.net.
I know it has something to do with sp_Who but not sure how the syntax will
fit in.
> Note MyConnectionString is something like:
> "data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ;
Password = MyPassword"
> Please help. What should I do so that this works in my vb.net app.
I am using MSDE with my application and providing our users UI to backup/res
tore the database. My app has just 1 database and 1 login mapped to 1 user
(MyAppUser). Backup and restore functionality is using inline sql commands.
Backup works fine with so
mething like this:
Private Sub Backup()
Dim cn As New SqlConnection(MyConnectionString)
Try
cn.Open()
Dim cm As New SqlCommand
With cm
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "BACKUP DATABASE MyDB TO DISK = 'D:\Backup\a.bak' WITH INIT"
.ExecuteNonQuery()
End With
MsgBox("Database backed up successfully!")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cn.Close()
End Try
End Sub
but when I do restore using something like this:
Private Sub Restore()
Dim cn As New SqlConnection(MyConnectionString)
Try
cn.Open()
Dim cm As New SqlCommand
With cm
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "RESTORE DATABASE MyDB FROM DISK = 'D:\Backup\a.bak' WITH RE
COVERY"
.ExecuteNonQuery()
End With
MsgBox("Database restored successfully!")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cn.Close()
End Try
End Sub
I get the "exclusive access could not be obtained.. .. database is in use" e
rror message.
I did "Use Master" in query analyser and ran the same restore sql command an
d it worked fine. I do not know how to use "Use master" here in ado.net. I
know it has something to do with sp_Who but not sure how the syntax will fi
t in.
Note MyConnectionString is something like:
"data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ; Pa
ssword = MyPassword"
Please help. What should I do so that this works in my vb.net app.You can use cn.ChangeDatabase("master") to change database
If you are restoring over an existing database you will also need specify
WITH REPLACE.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:C800D8D0-0D15-4352-8545-9CA47C9B4710@.microsoft.com...
> Hello,
> I am using MSDE with my application and providing our users UI to
backup/restore the database. My app has just 1 database and 1 login mapped
to 1 user (MyAppUser). Backup and restore functionality is using inline sql
commands. Backup works fine with something like this:
> Private Sub Backup()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "BACKUP DATABASE MyDB TO DISK =
'D:\Backup\a.bak' WITH INIT"
> .ExecuteNonQuery()
> End With
> MsgBox("Database backed up successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> but when I do restore using something like this:
> Private Sub Restore()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "RESTORE DATABASE MyDB FROM DISK =
'D:\Backup\a.bak' WITH RECOVERY"
> .ExecuteNonQuery()
> End With
> MsgBox("Database restored successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> I get the "exclusive access could not be obtained.. .. database is in use"
error message.
> I did "Use Master" in query analyser and ran the same restore sql command
and it worked fine. I do not know how to use "Use master" here in ado.net.
I know it has something to do with sp_Who but not sure how the syntax will
fit in.
> Note MyConnectionString is something like:
> "data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ;
Password = MyPassword"
> Please help. What should I do so that this works in my vb.net app.
"exclusive access could not be obtained.." while restoring
Hello,
I am using MSDE with my application and providing our users UI to backup/restore the database. My app has just 1 database and 1 login mapped to 1 user (MyAppUser). Backup and restore functionality is using inline sql commands. Backup works fine with so
mething like this:
Private Sub Backup()
Dim cn As New SqlConnection(MyConnectionString)
Try
cn.Open()
Dim cm As New SqlCommand
With cm
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "BACKUP DATABASE MyDB TO DISK = 'D:\Backup\a.bak' WITH INIT"
.ExecuteNonQuery()
End With
MsgBox("Database backed up successfully!")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cn.Close()
End Try
End Sub
but when I do restore using something like this:
Private Sub Restore()
Dim cn As New SqlConnection(MyConnectionString)
Try
cn.Open()
Dim cm As New SqlCommand
With cm
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "RESTORE DATABASE MyDB FROM DISK = 'D:\Backup\a.bak' WITH RECOVERY"
.ExecuteNonQuery()
End With
MsgBox("Database restored successfully!")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cn.Close()
End Try
End Sub
I get the "exclusive access could not be obtained.. .. database is in use" error message.
I did "Use Master" in query analyser and ran the same restore sql command and it worked fine. I do not know how to use "Use master" here in ado.net. I know it has something to do with sp_Who but not sure how the syntax will fit in.
Note MyConnectionString is something like:
"data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ; Password = MyPassword"
Please help. What should I do so that this works in my vb.net app.
You can use cn.ChangeDatabase("master") to change database
If you are restoring over an existing database you will also need specify
WITH REPLACE.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:C800D8D0-0D15-4352-8545-9CA47C9B4710@.microsoft.com...
> Hello,
> I am using MSDE with my application and providing our users UI to
backup/restore the database. My app has just 1 database and 1 login mapped
to 1 user (MyAppUser). Backup and restore functionality is using inline sql
commands. Backup works fine with something like this:
> Private Sub Backup()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "BACKUP DATABASE MyDB TO DISK =
'D:\Backup\a.bak' WITH INIT"
> .ExecuteNonQuery()
> End With
> MsgBox("Database backed up successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> but when I do restore using something like this:
> Private Sub Restore()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "RESTORE DATABASE MyDB FROM DISK =
'D:\Backup\a.bak' WITH RECOVERY"
> .ExecuteNonQuery()
> End With
> MsgBox("Database restored successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> I get the "exclusive access could not be obtained.. .. database is in use"
error message.
> I did "Use Master" in query analyser and ran the same restore sql command
and it worked fine. I do not know how to use "Use master" here in ado.net.
I know it has something to do with sp_Who but not sure how the syntax will
fit in.
> Note MyConnectionString is something like:
> "data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ;
Password = MyPassword"
> Please help. What should I do so that this works in my vb.net app.
I am using MSDE with my application and providing our users UI to backup/restore the database. My app has just 1 database and 1 login mapped to 1 user (MyAppUser). Backup and restore functionality is using inline sql commands. Backup works fine with so
mething like this:
Private Sub Backup()
Dim cn As New SqlConnection(MyConnectionString)
Try
cn.Open()
Dim cm As New SqlCommand
With cm
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "BACKUP DATABASE MyDB TO DISK = 'D:\Backup\a.bak' WITH INIT"
.ExecuteNonQuery()
End With
MsgBox("Database backed up successfully!")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cn.Close()
End Try
End Sub
but when I do restore using something like this:
Private Sub Restore()
Dim cn As New SqlConnection(MyConnectionString)
Try
cn.Open()
Dim cm As New SqlCommand
With cm
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "RESTORE DATABASE MyDB FROM DISK = 'D:\Backup\a.bak' WITH RECOVERY"
.ExecuteNonQuery()
End With
MsgBox("Database restored successfully!")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cn.Close()
End Try
End Sub
I get the "exclusive access could not be obtained.. .. database is in use" error message.
I did "Use Master" in query analyser and ran the same restore sql command and it worked fine. I do not know how to use "Use master" here in ado.net. I know it has something to do with sp_Who but not sure how the syntax will fit in.
Note MyConnectionString is something like:
"data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ; Password = MyPassword"
Please help. What should I do so that this works in my vb.net app.
You can use cn.ChangeDatabase("master") to change database
If you are restoring over an existing database you will also need specify
WITH REPLACE.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:C800D8D0-0D15-4352-8545-9CA47C9B4710@.microsoft.com...
> Hello,
> I am using MSDE with my application and providing our users UI to
backup/restore the database. My app has just 1 database and 1 login mapped
to 1 user (MyAppUser). Backup and restore functionality is using inline sql
commands. Backup works fine with something like this:
> Private Sub Backup()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "BACKUP DATABASE MyDB TO DISK =
'D:\Backup\a.bak' WITH INIT"
> .ExecuteNonQuery()
> End With
> MsgBox("Database backed up successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> but when I do restore using something like this:
> Private Sub Restore()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "RESTORE DATABASE MyDB FROM DISK =
'D:\Backup\a.bak' WITH RECOVERY"
> .ExecuteNonQuery()
> End With
> MsgBox("Database restored successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> I get the "exclusive access could not be obtained.. .. database is in use"
error message.
> I did "Use Master" in query analyser and ran the same restore sql command
and it worked fine. I do not know how to use "Use master" here in ado.net.
I know it has something to do with sp_Who but not sure how the syntax will
fit in.
> Note MyConnectionString is something like:
> "data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ;
Password = MyPassword"
> Please help. What should I do so that this works in my vb.net app.
Thursday, February 9, 2012
"cannot open user default database. Login failed"
When attempting to connect to my local instance of SQL server I am getting t
his error message: "cannot open user default database. Login failed".
My OS is WinXp v2002 SP2. This issue started after I deleted a database on
my local server, prior to that it was working fine.
ThanksIt looks like you deleted the database that was the default database for
your login. One method to fix the problem is to connect using the OSQL
command-line utility, overriding the default database. You can then change
the default DB to one that exists.
The example below connects to the local server with SQL authentication and
changes the default database to 'master':
OSQL -d master -U MyLogin -P MyPassword -Q"EXEC sp_defaultdb 'MyLogin'
,'master'"
Hope this helps.
Dan Guzman
SQL Server MVP
"Brent Stevenson" <essexbs@.insightbb.com> wrote in message
news:u6aIYXRwFHA.3864@.TK2MSFTNGP12.phx.gbl...
When attempting to connect to my local instance of SQL server I am getting
this error message: "cannot open user default database. Login failed".
My OS is WinXp v2002 SP2. This issue started after I deleted a database on
my local server, prior to that it was working fine.
Thanks|||Dan,
Thanks for the reply! But infortunaley that fails with a "login failed for
'user' error."
According to OSQL help: "If neither the -U or -P options are used, SQL
Server 2000 attempts to connect using Windows Authentication Mode." Well
that would be my network account & password and if I just enter OSQL -d
master and then my network account password at the prompt, it fails (login
failed for user). I know I'm correctly entering my current network account
password.
Any ideas?
Thanks
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:Oex4GfRwFHA.464@.TK2MSFTNGP15.phx.gbl...
> It looks like you deleted the database that was the default database for
> your login. One method to fix the problem is to connect using the OSQL
> command-line utility, overriding the default database. You can then
> change the default DB to one that exists.
> The example below connects to the local server with SQL authentication and
> changes the default database to 'master':
> OSQL -d master -U MyLogin -P MyPassword -Q"EXEC sp_defaultdb 'MyLogin'
> ,'master'"
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Brent Stevenson" <essexbs@.insightbb.com> wrote in message
> news:u6aIYXRwFHA.3864@.TK2MSFTNGP12.phx.gbl...
> When attempting to connect to my local instance of SQL server I am getting
> this error message: "cannot open user default database. Login failed".
> My OS is WinXp v2002 SP2. This issue started after I deleted a database
> on my local server, prior to that it was working fine.
> Thanks
>|||My machines gives an error if I don't use -U or -E. Did you try using the -E
switch to login using
your Windows account?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brent Stevenson" <essexbs@.insightbb.com> wrote in message
news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Dan,
> Thanks for the reply! But infortunaley that fails with a "login failed for
'user' error."
> According to OSQL help: "If neither the -U or -P options are used, SQL Ser
ver 2000 attempts to
> connect using Windows Authentication Mode." Well that would be my network
account & password and
> if I just enter OSQL -d master and then my network account password at the
prompt, it fails (login
> failed for user). I know I'm correctly entering my current network account
password.
> Any ideas?
> Thanks
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:Oex4GfRwFHA.464@.TK2MSFTNGP15.phx.gbl...
>|||Using the -E go me in & allowed me to change my default DB & resolve the
issue.
Thanks a million!! Have a good day.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23vKLtxdwFHA.3556@.TK2MSFTNGP12.phx.gbl...
> My machines gives an error if I don't use -U or -E. Did you try using
> the -E switch to login using your Windows account?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Brent Stevenson" <essexbs@.insightbb.com> wrote in message
> news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
>|||The example I posted assumed a SQL login. Like Tibor mentioned, you can
specify '-E' to establish a trusted connection using your current Windows
account. You never need to specify a Windows account with the '-E'
parameter but you still need to specify the name of the account to
sp_defaultdb. For a local Windows account:
OSQL -d master -E -Q"EXEC sp_defaultdb 'MyMachine\MyAccount','master'"
or for a domain account:
OSQL -d master -E -Q"EXEC sp_defaultdb 'MyDomain\MyAccount','master'"
If your SQL Server access is via membership of the local Administrators
group, you'll need to change the default database for that group instead:
OSQL -d master -E -Q"EXEC sp_defaultdb 'BUILTIN\Administrators','master'"
Hope this helps.
Dan Guzman
SQL Server MVP
"Brent Stevenson" <essexbs@.insightbb.com> wrote in message
news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Dan,
> Thanks for the reply! But infortunaley that fails with a "login failed for
> 'user' error."
> According to OSQL help: "If neither the -U or -P options are used, SQL
> Server 2000 attempts to connect using Windows Authentication Mode." Well
> that would be my network account & password and if I just enter OSQL -d
> master and then my network account password at the prompt, it fails (login
> failed for user). I know I'm correctly entering my current network account
> password.
> Any ideas?
> Thanks
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:Oex4GfRwFHA.464@.TK2MSFTNGP15.phx.gbl...
>|||I just had the same problem, and used this same solution. Deleted a
database, didn't realise it was my default DB (or that it mattered), and
couldn't figure out the account credentials problem I was having using the
command line suggestions found elsewhere. Was using a domain admin account.
Thanks very much!
Bryan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u9z7NBewFHA.2540@.TK2MSFTNGP09.phx.gbl...
> The example I posted assumed a SQL login. Like Tibor mentioned, you can
> specify '-E' to establish a trusted connection using your current Windows
> account. You never need to specify a Windows account with the '-E'
> parameter but you still need to specify the name of the account to
> sp_defaultdb. For a local Windows account:
> OSQL -d master -E -Q"EXEC sp_defaultdb 'MyMachine\MyAccount','master'"
> or for a domain account:
> OSQL -d master -E -Q"EXEC sp_defaultdb 'MyDomain\MyAccount','master'"
> If your SQL Server access is via membership of the local Administrators
> group, you'll need to change the default database for that group instead:
> OSQL -d master -E -Q"EXEC sp_defaultdb 'BUILTIN\Administrators','master'"
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Brent Stevenson" <essexbs@.insightbb.com> wrote in message
> news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
>
his error message: "cannot open user default database. Login failed".
My OS is WinXp v2002 SP2. This issue started after I deleted a database on
my local server, prior to that it was working fine.
ThanksIt looks like you deleted the database that was the default database for
your login. One method to fix the problem is to connect using the OSQL
command-line utility, overriding the default database. You can then change
the default DB to one that exists.
The example below connects to the local server with SQL authentication and
changes the default database to 'master':
OSQL -d master -U MyLogin -P MyPassword -Q"EXEC sp_defaultdb 'MyLogin'
,'master'"
Hope this helps.
Dan Guzman
SQL Server MVP
"Brent Stevenson" <essexbs@.insightbb.com> wrote in message
news:u6aIYXRwFHA.3864@.TK2MSFTNGP12.phx.gbl...
When attempting to connect to my local instance of SQL server I am getting
this error message: "cannot open user default database. Login failed".
My OS is WinXp v2002 SP2. This issue started after I deleted a database on
my local server, prior to that it was working fine.
Thanks|||Dan,
Thanks for the reply! But infortunaley that fails with a "login failed for
'user' error."
According to OSQL help: "If neither the -U or -P options are used, SQL
Server 2000 attempts to connect using Windows Authentication Mode." Well
that would be my network account & password and if I just enter OSQL -d
master and then my network account password at the prompt, it fails (login
failed for user). I know I'm correctly entering my current network account
password.
Any ideas?
Thanks
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:Oex4GfRwFHA.464@.TK2MSFTNGP15.phx.gbl...
> It looks like you deleted the database that was the default database for
> your login. One method to fix the problem is to connect using the OSQL
> command-line utility, overriding the default database. You can then
> change the default DB to one that exists.
> The example below connects to the local server with SQL authentication and
> changes the default database to 'master':
> OSQL -d master -U MyLogin -P MyPassword -Q"EXEC sp_defaultdb 'MyLogin'
> ,'master'"
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Brent Stevenson" <essexbs@.insightbb.com> wrote in message
> news:u6aIYXRwFHA.3864@.TK2MSFTNGP12.phx.gbl...
> When attempting to connect to my local instance of SQL server I am getting
> this error message: "cannot open user default database. Login failed".
> My OS is WinXp v2002 SP2. This issue started after I deleted a database
> on my local server, prior to that it was working fine.
> Thanks
>|||My machines gives an error if I don't use -U or -E. Did you try using the -E
switch to login using
your Windows account?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brent Stevenson" <essexbs@.insightbb.com> wrote in message
news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Dan,
> Thanks for the reply! But infortunaley that fails with a "login failed for
'user' error."
> According to OSQL help: "If neither the -U or -P options are used, SQL Ser
ver 2000 attempts to
> connect using Windows Authentication Mode." Well that would be my network
account & password and
> if I just enter OSQL -d master and then my network account password at the
prompt, it fails (login
> failed for user). I know I'm correctly entering my current network account
password.
> Any ideas?
> Thanks
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:Oex4GfRwFHA.464@.TK2MSFTNGP15.phx.gbl...
>|||Using the -E go me in & allowed me to change my default DB & resolve the
issue.
Thanks a million!! Have a good day.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23vKLtxdwFHA.3556@.TK2MSFTNGP12.phx.gbl...
> My machines gives an error if I don't use -U or -E. Did you try using
> the -E switch to login using your Windows account?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Brent Stevenson" <essexbs@.insightbb.com> wrote in message
> news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
>|||The example I posted assumed a SQL login. Like Tibor mentioned, you can
specify '-E' to establish a trusted connection using your current Windows
account. You never need to specify a Windows account with the '-E'
parameter but you still need to specify the name of the account to
sp_defaultdb. For a local Windows account:
OSQL -d master -E -Q"EXEC sp_defaultdb 'MyMachine\MyAccount','master'"
or for a domain account:
OSQL -d master -E -Q"EXEC sp_defaultdb 'MyDomain\MyAccount','master'"
If your SQL Server access is via membership of the local Administrators
group, you'll need to change the default database for that group instead:
OSQL -d master -E -Q"EXEC sp_defaultdb 'BUILTIN\Administrators','master'"
Hope this helps.
Dan Guzman
SQL Server MVP
"Brent Stevenson" <essexbs@.insightbb.com> wrote in message
news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Dan,
> Thanks for the reply! But infortunaley that fails with a "login failed for
> 'user' error."
> According to OSQL help: "If neither the -U or -P options are used, SQL
> Server 2000 attempts to connect using Windows Authentication Mode." Well
> that would be my network account & password and if I just enter OSQL -d
> master and then my network account password at the prompt, it fails (login
> failed for user). I know I'm correctly entering my current network account
> password.
> Any ideas?
> Thanks
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:Oex4GfRwFHA.464@.TK2MSFTNGP15.phx.gbl...
>|||I just had the same problem, and used this same solution. Deleted a
database, didn't realise it was my default DB (or that it mattered), and
couldn't figure out the account credentials problem I was having using the
command line suggestions found elsewhere. Was using a domain admin account.
Thanks very much!
Bryan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u9z7NBewFHA.2540@.TK2MSFTNGP09.phx.gbl...
> The example I posted assumed a SQL login. Like Tibor mentioned, you can
> specify '-E' to establish a trusted connection using your current Windows
> account. You never need to specify a Windows account with the '-E'
> parameter but you still need to specify the name of the account to
> sp_defaultdb. For a local Windows account:
> OSQL -d master -E -Q"EXEC sp_defaultdb 'MyMachine\MyAccount','master'"
> or for a domain account:
> OSQL -d master -E -Q"EXEC sp_defaultdb 'MyDomain\MyAccount','master'"
> If your SQL Server access is via membership of the local Administrators
> group, you'll need to change the default database for that group instead:
> OSQL -d master -E -Q"EXEC sp_defaultdb 'BUILTIN\Administrators','master'"
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Brent Stevenson" <essexbs@.insightbb.com> wrote in message
> news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
>
"cannot open user default database. Login failed"
When attempting to connect to my local instance of SQL server I am getting this error message: "cannot open user default database. Login failed".
My OS is WinXp v2002 SP2. This issue started after I deleted a database on my local server, prior to that it was working fine.
Thanks
It looks like you deleted the database that was the default database for
your login. One method to fix the problem is to connect using the OSQL
command-line utility, overriding the default database. You can then change
the default DB to one that exists.
The example below connects to the local server with SQL authentication and
changes the default database to 'master':
OSQL -d master -U MyLogin -P MyPassword -Q"EXEC sp_defaultdb 'MyLogin'
,'master'"
Hope this helps.
Dan Guzman
SQL Server MVP
"Brent Stevenson" <essexbs@.insightbb.com> wrote in message
news:u6aIYXRwFHA.3864@.TK2MSFTNGP12.phx.gbl...
When attempting to connect to my local instance of SQL server I am getting
this error message: "cannot open user default database. Login failed".
My OS is WinXp v2002 SP2. This issue started after I deleted a database on
my local server, prior to that it was working fine.
Thanks
|||Dan,
Thanks for the reply! But infortunaley that fails with a "login failed for
'user' error."
According to OSQL help: "If neither the -U or -P options are used, SQL
Server 2000 attempts to connect using Windows Authentication Mode." Well
that would be my network account & password and if I just enter OSQL -d
master and then my network account password at the prompt, it fails (login
failed for user). I know I'm correctly entering my current network account
password.
Any ideas?
Thanks
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:Oex4GfRwFHA.464@.TK2MSFTNGP15.phx.gbl...
> It looks like you deleted the database that was the default database for
> your login. One method to fix the problem is to connect using the OSQL
> command-line utility, overriding the default database. You can then
> change the default DB to one that exists.
> The example below connects to the local server with SQL authentication and
> changes the default database to 'master':
> OSQL -d master -U MyLogin -P MyPassword -Q"EXEC sp_defaultdb 'MyLogin'
> ,'master'"
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Brent Stevenson" <essexbs@.insightbb.com> wrote in message
> news:u6aIYXRwFHA.3864@.TK2MSFTNGP12.phx.gbl...
> When attempting to connect to my local instance of SQL server I am getting
> this error message: "cannot open user default database. Login failed".
> My OS is WinXp v2002 SP2. This issue started after I deleted a database
> on my local server, prior to that it was working fine.
> Thanks
>
|||My machines gives an error if I don't use -U or -E. Did you try using the -E switch to login using
your Windows account?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brent Stevenson" <essexbs@.insightbb.com> wrote in message
news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Dan,
> Thanks for the reply! But infortunaley that fails with a "login failed for 'user' error."
> According to OSQL help: "If neither the -U or -P options are used, SQL Server 2000 attempts to
> connect using Windows Authentication Mode." Well that would be my network account & password and
> if I just enter OSQL -d master and then my network account password at the prompt, it fails (login
> failed for user). I know I'm correctly entering my current network account password.
> Any ideas?
> Thanks
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:Oex4GfRwFHA.464@.TK2MSFTNGP15.phx.gbl...
>
|||Using the -E go me in & allowed me to change my default DB & resolve the
issue.
Thanks a million!! Have a good day.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23vKLtxdwFHA.3556@.TK2MSFTNGP12.phx.gbl...
> My machines gives an error if I don't use -U or -E. Did you try using
> the -E switch to login using your Windows account?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Brent Stevenson" <essexbs@.insightbb.com> wrote in message
> news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
>
|||The example I posted assumed a SQL login. Like Tibor mentioned, you can
specify '-E' to establish a trusted connection using your current Windows
account. You never need to specify a Windows account with the '-E'
parameter but you still need to specify the name of the account to
sp_defaultdb. For a local Windows account:
OSQL -d master -E -Q"EXEC sp_defaultdb 'MyMachine\MyAccount','master'"
or for a domain account:
OSQL -d master -E -Q"EXEC sp_defaultdb 'MyDomain\MyAccount','master'"
If your SQL Server access is via membership of the local Administrators
group, you'll need to change the default database for that group instead:
OSQL -d master -E -Q"EXEC sp_defaultdb 'BUILTIN\Administrators','master'"
Hope this helps.
Dan Guzman
SQL Server MVP
"Brent Stevenson" <essexbs@.insightbb.com> wrote in message
news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Dan,
> Thanks for the reply! But infortunaley that fails with a "login failed for
> 'user' error."
> According to OSQL help: "If neither the -U or -P options are used, SQL
> Server 2000 attempts to connect using Windows Authentication Mode." Well
> that would be my network account & password and if I just enter OSQL -d
> master and then my network account password at the prompt, it fails (login
> failed for user). I know I'm correctly entering my current network account
> password.
> Any ideas?
> Thanks
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:Oex4GfRwFHA.464@.TK2MSFTNGP15.phx.gbl...
>
|||I just had the same problem, and used this same solution. Deleted a
database, didn't realise it was my default DB (or that it mattered), and
couldn't figure out the account credentials problem I was having using the
command line suggestions found elsewhere. Was using a domain admin account.
Thanks very much!
Bryan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u9z7NBewFHA.2540@.TK2MSFTNGP09.phx.gbl...
> The example I posted assumed a SQL login. Like Tibor mentioned, you can
> specify '-E' to establish a trusted connection using your current Windows
> account. You never need to specify a Windows account with the '-E'
> parameter but you still need to specify the name of the account to
> sp_defaultdb. For a local Windows account:
> OSQL -d master -E -Q"EXEC sp_defaultdb 'MyMachine\MyAccount','master'"
> or for a domain account:
> OSQL -d master -E -Q"EXEC sp_defaultdb 'MyDomain\MyAccount','master'"
> If your SQL Server access is via membership of the local Administrators
> group, you'll need to change the default database for that group instead:
> OSQL -d master -E -Q"EXEC sp_defaultdb 'BUILTIN\Administrators','master'"
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Brent Stevenson" <essexbs@.insightbb.com> wrote in message
> news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
>
My OS is WinXp v2002 SP2. This issue started after I deleted a database on my local server, prior to that it was working fine.
Thanks
It looks like you deleted the database that was the default database for
your login. One method to fix the problem is to connect using the OSQL
command-line utility, overriding the default database. You can then change
the default DB to one that exists.
The example below connects to the local server with SQL authentication and
changes the default database to 'master':
OSQL -d master -U MyLogin -P MyPassword -Q"EXEC sp_defaultdb 'MyLogin'
,'master'"
Hope this helps.
Dan Guzman
SQL Server MVP
"Brent Stevenson" <essexbs@.insightbb.com> wrote in message
news:u6aIYXRwFHA.3864@.TK2MSFTNGP12.phx.gbl...
When attempting to connect to my local instance of SQL server I am getting
this error message: "cannot open user default database. Login failed".
My OS is WinXp v2002 SP2. This issue started after I deleted a database on
my local server, prior to that it was working fine.
Thanks
|||Dan,
Thanks for the reply! But infortunaley that fails with a "login failed for
'user' error."
According to OSQL help: "If neither the -U or -P options are used, SQL
Server 2000 attempts to connect using Windows Authentication Mode." Well
that would be my network account & password and if I just enter OSQL -d
master and then my network account password at the prompt, it fails (login
failed for user). I know I'm correctly entering my current network account
password.
Any ideas?
Thanks
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:Oex4GfRwFHA.464@.TK2MSFTNGP15.phx.gbl...
> It looks like you deleted the database that was the default database for
> your login. One method to fix the problem is to connect using the OSQL
> command-line utility, overriding the default database. You can then
> change the default DB to one that exists.
> The example below connects to the local server with SQL authentication and
> changes the default database to 'master':
> OSQL -d master -U MyLogin -P MyPassword -Q"EXEC sp_defaultdb 'MyLogin'
> ,'master'"
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Brent Stevenson" <essexbs@.insightbb.com> wrote in message
> news:u6aIYXRwFHA.3864@.TK2MSFTNGP12.phx.gbl...
> When attempting to connect to my local instance of SQL server I am getting
> this error message: "cannot open user default database. Login failed".
> My OS is WinXp v2002 SP2. This issue started after I deleted a database
> on my local server, prior to that it was working fine.
> Thanks
>
|||My machines gives an error if I don't use -U or -E. Did you try using the -E switch to login using
your Windows account?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brent Stevenson" <essexbs@.insightbb.com> wrote in message
news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Dan,
> Thanks for the reply! But infortunaley that fails with a "login failed for 'user' error."
> According to OSQL help: "If neither the -U or -P options are used, SQL Server 2000 attempts to
> connect using Windows Authentication Mode." Well that would be my network account & password and
> if I just enter OSQL -d master and then my network account password at the prompt, it fails (login
> failed for user). I know I'm correctly entering my current network account password.
> Any ideas?
> Thanks
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:Oex4GfRwFHA.464@.TK2MSFTNGP15.phx.gbl...
>
|||Using the -E go me in & allowed me to change my default DB & resolve the
issue.
Thanks a million!! Have a good day.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23vKLtxdwFHA.3556@.TK2MSFTNGP12.phx.gbl...
> My machines gives an error if I don't use -U or -E. Did you try using
> the -E switch to login using your Windows account?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Brent Stevenson" <essexbs@.insightbb.com> wrote in message
> news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
>
|||The example I posted assumed a SQL login. Like Tibor mentioned, you can
specify '-E' to establish a trusted connection using your current Windows
account. You never need to specify a Windows account with the '-E'
parameter but you still need to specify the name of the account to
sp_defaultdb. For a local Windows account:
OSQL -d master -E -Q"EXEC sp_defaultdb 'MyMachine\MyAccount','master'"
or for a domain account:
OSQL -d master -E -Q"EXEC sp_defaultdb 'MyDomain\MyAccount','master'"
If your SQL Server access is via membership of the local Administrators
group, you'll need to change the default database for that group instead:
OSQL -d master -E -Q"EXEC sp_defaultdb 'BUILTIN\Administrators','master'"
Hope this helps.
Dan Guzman
SQL Server MVP
"Brent Stevenson" <essexbs@.insightbb.com> wrote in message
news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Dan,
> Thanks for the reply! But infortunaley that fails with a "login failed for
> 'user' error."
> According to OSQL help: "If neither the -U or -P options are used, SQL
> Server 2000 attempts to connect using Windows Authentication Mode." Well
> that would be my network account & password and if I just enter OSQL -d
> master and then my network account password at the prompt, it fails (login
> failed for user). I know I'm correctly entering my current network account
> password.
> Any ideas?
> Thanks
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:Oex4GfRwFHA.464@.TK2MSFTNGP15.phx.gbl...
>
|||I just had the same problem, and used this same solution. Deleted a
database, didn't realise it was my default DB (or that it mattered), and
couldn't figure out the account credentials problem I was having using the
command line suggestions found elsewhere. Was using a domain admin account.
Thanks very much!
Bryan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u9z7NBewFHA.2540@.TK2MSFTNGP09.phx.gbl...
> The example I posted assumed a SQL login. Like Tibor mentioned, you can
> specify '-E' to establish a trusted connection using your current Windows
> account. You never need to specify a Windows account with the '-E'
> parameter but you still need to specify the name of the account to
> sp_defaultdb. For a local Windows account:
> OSQL -d master -E -Q"EXEC sp_defaultdb 'MyMachine\MyAccount','master'"
> or for a domain account:
> OSQL -d master -E -Q"EXEC sp_defaultdb 'MyDomain\MyAccount','master'"
> If your SQL Server access is via membership of the local Administrators
> group, you'll need to change the default database for that group instead:
> OSQL -d master -E -Q"EXEC sp_defaultdb 'BUILTIN\Administrators','master'"
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Brent Stevenson" <essexbs@.insightbb.com> wrote in message
> news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
>
"C:\INETPUB\WWWROOT\SLEPCHA\APP_DATA\ASPNETDB.MDF" because the database is read-only.
i have created a appl and deployed it in IIS .
When i login from the login page i get a error :Server Error in '/slepcha' Application.
------------------------
Failed to update database "C:\INETPUB\WWWROOT\SLEPCHA\APP_DATA\ASPNETDB.MDF" because the database is read-only.
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: Failed to update database "C:\INETPUB\WWWROOT\SLEPCHA\APP_DATA\ASPNETDB.MDF" because the database is read-only.
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:
Have you checked to see whether that file has the ReadOnly attribute set?
Subscribe to:
Posts (Atom)
