Not Able To Login After Changing The Authetication Mode
Jul 8, 2007
Hi all,
This is my first post and I am a newbie user of SQL Server as well. I am using SQL Server 2005 Express edition. I have also installed SQL Server 2005 Mangement Studio express.
Initially I was under Windows Authetication but today I changed it to mix mode. After doing everything and enabling the authentication for the "sa" user I am getting the following error.
In the login dialog window for the user name I am providing: sa
TITLE: Connect to Server
------------------------------
A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)
When I try the second time it gives this error:
TITLE: Connect to Server
------------------------------
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_md_06_35bq.asp are
"Autocommit mode is the default transaction management mode of Microsoft® SQL Server„¢. Every Transact-SQL statement is committed or rolled back when it completes."
"if a statement completes successfully, it is committed; if it encounters any error, it is rolled back. A SQL Server connection operates in autocommit mode whenever this default mode has not been overridden by either explicit or implicit transactions."
My question is, how do we change autocommit mode. I don't want the dml to be commited until i explicitly do commit. This should be default setting.
I want this change either to be made at database level, maybe by doing some setting or through some T-SQL stmt.
I DO NOT want to use anything that has to be done in query windows like set ... off | on in each and every query window i use.
The change has to be done only once and should persist through-out.
How can I change the authentication mode for sqlexpress without going through the UI? I looked at the docs for sp_configure, dmo and smo but I may have missed it. I'd like to be able to run a script that changes the authentication mode of an installed sqlexpress instance from windows only to moxed mode.
What is the impact of changing the recovery mode of a database from Full to simple? The client I am at has set their database to full recovery mode, set their log files to grow automatically. But I don't think they have ever done a backup of their transaction log (it has grown to over 19Gig, where the data portion of the database is only around 400M).
What is the impact of truncating the transaction log now? After truncating it, i would like to shrink the file to a managable number and change the recovery mode to simple (they don't need transaction log backups)
I have a 2005 database sitting on a 2012 server, we're looking to change its compatibility mode from 90 to 110 so we can avail of what 2012 offers.I did a migration project a couple years ago for SQL Server 2000 databases changing to 2008 R2 and we ran into loads of problems where we left most databases in compatibility mode 80 as these were application databases that the owners couldn't stand over in terms of deprecated code / features no longer in use in 2008 R2.
From what I can see with changing from 90 to 110, there doesn't seem to be as many issues but I just want to double check if there's a way to confirm this. I know upgrade advisor is a handy tool but will it pick up database specific issues as opposed to database server compatibility issues? URLs...I know UA won't cover all the bases but it would look good when attached to the report I'm submitting recommending the change.
I have a question about change of compability. I want to change compability level from 2000 to 2005 on a very large database with a lot of indexes. I have heard that if I change compability level the indexes will no longer be usable. Is it enough to just rebuild all indexed after I have changed compability level or do I have to do something else?
In a Data Flow Task, I have an insert that occurs into a SQL Server 2000 table from a fixed width flat file. The SQL Server table that the data goes into is accessed through an OLE DB connection manager that uses the Native OLE DBMicrosoft OLE DB Provider for SQL Server.
In the OLE DB Destination, I changed the access mode from Table or View - fast load to Table or View because I needed to implement OLE DB Destination Error Output. The Error output goes to a SQL Server 2000 table that uses the same connection manager.
The OLE DB Destination Editor Error Output 'Error' option is configured to 'Redirect' the row. 'Set this value to selected cells' is set to 'Fail component'.
Was changing the access mode the simple reason why the insert from the flat file takes so much longer, or could there be other problems?
Hi, I'm having a rough weekend and really need some advice. We've been having some problems with our SQL Server ever since an upgrade to SQL 2K and then were moved by the IT security people to a new domain that is not in a trust relationship with the domain the SQL Server is on. After the move, attempts to log into a CMMS application we use, called Maximo, are failing. The error indicated that the user's domain login isn't trusted with the SQL Server. This is odd, because we use SQL Server Authentication and each user has a different username on the SQL Server and shouldn't care about the domain login, so I don't know why it's even being passed. This application has been working fine on SQL Server 7 for the past 3 years.
Our SQL Server is setup for Mixed Mode (set during installation and verified in Ent Mgr) and the clients are using ODBC to connect with TCP/IP port 1433 (setup with the client configuration in the ODBC properties) using SQL Server Authentication. The problem is that the settings seem to be ignored and it's using Windows Authentication only, which is a major problem. All of the grants are given to the SQL Server user by the Maximo application and all records are tracked by the login, so we can't use Windows Authentication. I am able to add the user's domain login to SQL Server and give it RW rights to the database and the user is able to login, however unable to run reports because the grants are given to the SQL Server login, not the domain login.
I tried removing the server from the domain it was on and making it a stand alone server, which resulted in the following error:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
This is telling me that it's ignoring SQL Server Authentication. I've also tried installing Patch 3a on SQL Server and installing MDAC 2.8 on the client.
My school is runnnig SQL Server 2005 and i want to connect to it using authetication (userid & passphrase). As i coded the windows application i used localhost as my own computer and IntegratedSecurity shown below. I'm new in C# and SQL. I would appriciate if someone could help me out.
using System; using System.Data; using System.Data.SqlClient; using System.Text;
namespace Personregister { class DBManager { SqlConnection conn; SqlConnectionStringBuilder builder;
public DBManager() { builder = new SqlConnectionStringBuilder(); builder.DataSource="localhost"; builder.InitialCatalog = "Personregister"; builder.IntegratedSecurity = true; conn = new SqlConnection(builder.ToString());
I choose SQL and windows authetication during SQL Express 2005 install. Will this has any impact on the connection string in my Visual Studio 2005 environment?
Durning install I selected Window's Authentication only, but now it seems we may need to use a Mixed Mode with an SA account etc... is there anyway to switch SQL 2005 to use Mixed Mode after the fact?
We have reports in SharePoint integrated mode which are really slow when compared to native mode. I have been asked to research and give info on what exactly causes the delays.
Any articles which give me information as to what happens when a report is run from SharePoint server and where does it log.
There is a query which when executed in the grid mode(ctrl+d) takes approx 0.02 seconds(about 21,000 rows) But when I execute in the text mode, it takes about 0.40 seconds!! Why is this difference? Also, when the records from this table are read from a VB application, they are equally slow (as in the text mode!) Why is it so slow on the text mode & relatively faster in the grid mode? Has anyone got any idea on ‘Firehose’ style cursor ?(which may speed up access of data in the VB application)
Recently I read such statments 'When SQL Server is run in "lightweight pooling" mode (fiber mode) and the DTC service is started, unexpected behavior may occur.' Can someone say anything about fibe mode?I am appreciated for it.:)
Currently, our Report Builder is running on Local Intranet mode. I'm investigating what the security implications are in changing it to Internet mode. However, I've not been able to find any documentation on this.
Does anyone know of any documentation that addresses this issue or have experience that they can share with changing Report Builder security zone from Intranet mode to Internet mode?
I have a SQL 2005 OTC. CTP version running on Windows 2003 server.
I would like to find out how the SQL server option changed to Windows Authentication mode from Mixed mode over the weekend. From the SQL log, I don't see when it changed. I would like to see Date/Time and client IP. If I can see User ID (windows) that would be great. Where I can find these info in SQL server?
Hi, Im getting this error when attempting to retrieve data from an sql database.
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: Cannot open database requested in login 'projectAllocations'. Login fails. Login failed for user 'sa'.
Source Error:
Line 13: objConn = New SqlConnection( "Server=LAB303-066NETSDK; Database=projectAllocations; User ID=sa;Password=mypassword") Line 14: objCmd = New SqlCommand("SELECT * FROM project_descriptions", objConn) Line 15: objConn.Open() Line 16: objRdr = objCmd.ExecuteReader() Line 17: While objRdr.Read()
Been looking through the forums for a solution to this problem.I already tried granting access through statements such as:exec sp_grantloginaccess N1'machineNameASPNET'But they don't seem to work.. i vaguely remember seeing somewhere a DOS command line statement that grants access to the ASPNET_WP and that fixed my problem before on another computer.. but this is a new computer and i forgot to write down the command.Can anyone help explain and propose a solution to my problem. Many thanxs.
I am using the MSDE to connect to my ASP.NET application. I get this error after clicking the login button of my login page. Anyone know why this would happen?
Thanks for any help,
Cannot open database requested in login 'DataSQL'. Login fails. Login failed for user 'serverASPNET'.
hi, so i have a new box and I'm trying to get my websites and SQL Server 2005 Standard Edition working on it, but the pages give me the following error when I try to load them: "Cannot Open Database "XXXX" requested by login. The login failed. Login failed for user 'xxx'" Everything seems exactly the same settings and user-wise from my old box to my new one, but nevertheless everything I've tried gives me the same error. I've tried creating new users in SQL Server and giving them appropriate permissions to my database. I've even tried just using the built in 'sa' account. Nothing seems to change the error, except when I give it the incorrect password then it just says 'login failed' This leads me to believe that i'm successfully logging into the SQL Server, but it doesn't want to give me access to the database I'm requesting access too. But "apparently" the account i'm using should have access to the database. If nothing else the 'sa' account should, but that didn't work either. I'm stumped. Any ideas?
Hi I have asp.net2 app using SQLExpress which works fine in VS2005. When I compile it and try to run it from IIS on the same machine using the same sql server database with: connectionstring="server=.SQLEXPRESS;Database=abc.mdf;Trusted_Connection=yes"/> I get the following error. Cannot open database abc.mdf requested by login. The login failed. Login failed for user 'machinenameASPNET' Any help much appreciated
I have a script component. What it does, it queries the a table using a connectionstring assigned to it in the Connection Managers Editor (which is an ado.net adapter). this works fine when i'm using a windows login in the ado.net connection manager. But when i changed my connection to use SQL server login, I encounter this error:
OnError,,,Add new records to Dim_T_Status (Case),,,10/14/2007 5:54:47 PM,10/14/2007 5:54:47 PM,-1073450910,0x,System.Data.SqlClient.SqlException: Login failed for user 'CS_REPORT'. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket) OnError,,,LOAD AND UPDATE OCEAN Dimension Tables,,,10/14/2007 5:54:47 PM,10/14/2007 5:54:47 PM,-1073450910,0x,System.Data.SqlClient.SqlException: Login failed for user 'CS_REPORT'. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket) OnError,,,LoadOCEANDimensions,,,10/14/2007 5:54:47 PM,10/14/2007 5:54:47 PM,-1073450910,0x,System.Data.SqlClient.SqlException: Login failed for user 'CS_REPORT'. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)
Cannot open database "QuoteSystem" requested by the login. The login failed.Login failed for user 'NT AUTHORITYNETWORK SERVICE'. Last time I recieved this error I had to add ASPNet, NTAuthority, and myWebSiteUsers USERS under the database properties-->permissions setting for the specific database I am trying to access with the website but this database(QuoteSystem) does not lists any of these objects so I can add them. Does anybody know what I have to do to fix this? I am using SQL Server 2005.
Please, can anyone tell me why I am getting the undermentioned error message when I develop my application as an IIS Site in Visual Web Developer 2005 Express ? I have already developed and successfully tested it as a 'FileSystem Web Site' .I am using SQLSErver Express 2005 edition and have which I have installed along with the Northwind database as per the download instructions. My IIS software is version 5.1 which I have checked and it is configured to allow Integrated Windows Authentication. the relevant code is (1) web.config file connection strings <connectionStrings> <add name="NorthwindConnectionString" connectionString="Data Source=.sqlexpress;Initial Catalog=Northwind;Integrated Security=True" providerName="System.Data.SqlClient"/> </connectionStrings>(2) the grid view control and sqldatasource <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="CategoryID" DataSourceID="SqlDataSource1"> <Columns> <asp:BoundField DataField="CategoryID" HeaderText="CategoryID" InsertVisible="False" ReadOnly="True" SortExpression="CategoryID" /> <asp:BoundField DataField="CategoryName" HeaderText="CategoryName" SortExpression="CategoryName" /> <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" SelectCommand="SELECT [CategoryID], [CategoryName], [Description] FROM [Categories]"> </asp:SqlDataSource>This is the full error message and details: Server Error in '/sqlservertest' Application.
Cannot open database "Northwind" requested by the login. The login failed.Login failed for user 'JERRY-3C9615BAAASPNET'. 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: Cannot open database "Northwind" requested by the login. The login failed.Login failed for user 'JERRY-3C9615BAAASPNET'.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:
All the logins with sysadmin role are locked out in one of the SQL server. Also in that instance Logon trigger is implemented which allows only certain users with Windows Authentication to logon.
This server is converted into single user mode by using command prompt. Now we are attempting to connect to SQLServer using command sqlcmd -S FTCPU3239 (since we do not have any other login) and create a new login with sysadmin role but are refused due to logon trigger. it says Login failed for login 'DomainNameUserName' due to trigger execution.
I have a server that has 20 databases . I have tested with few users with different level of access and all of them were able to connect to the server and also see, select, update , delete from a particular database which is kind of weird because they do not have a user login associated or mapped to that database. I checked and no user is part of any group in AD that would give them permission to connect . I need a query that would find the permission path of a user. I already queried with xp_logininfo but I am not getting any thing.
Hi all, Iam getting this error System.Data.SqlClient.SqlException: Cannot open database "XYZ" requested by the login. The login failed.Login failed for user 'xyz-abcASPNET'. when trying to open the page.... http://localhost:1807/projectname/WebFormName.aspx as http://localhost/projectname/WebFormName.aspx Couldnt figure out the solution.Please help Soujanya
Hi, I just installed Visual web developper 2008 express with all options including SQL srvr express 2005!
This is from default iso image on the official website.
I create my first project and when I want to do ASP.NET configuration from web site menu, i got this error:
"Cannot open database "aspnetdb" requested by the login. The login failed."
I am just trying to learn here so i even did not create any dB or something, it is 1st time I run configuration to add security roles and users for testing!
I also un-installed all and re-installed, still the same...
