It is our yearly procedure to change the sa password.
This time we do it for the first time in ss2005.
After changing the sa password, all jobs fail with the following error:
MessageLogin failed for user 'sa'. [CLIENT: <local machine>]
MessageError: 18456, Severity: 14, State: 8.
When i check the properties of the job i can see (in the general tab) that all jobs are running through Windows authentication.
Any newly created jobs run successfull.
What do i do wrong?
In the server agent properties I use the Test button and I receive the response. "Successfully started(and stopped) a mail session with this profile." Indicating that mail should work.
If I send a email notification from a scheduled job it gives me the error: NOTE: Failed to notify 'operations' via email
In the SQL Server Agent Error log I get. "[264] An attempt was made to send an email when no email session has been established"
Today when I came into work, I noticed all of my jobs failed on a particular server. I tried to manually kick off the packages, but to no avail. Both the job history and the error message from the packages state a timeout error. Any idea on this? Also, I have no problem running a query from query analyzer...even on a remote machine. Any help would be appreciated Trevor
The SQL Server Agent process is running. When I use EM to start a database integrity check job, it fails with an error message of 22029 and the following:
[LOG] SQLServerAgent security context does not have server-autorestart privileges
I can not find error 22029 in Books on Line. I will go to Microsoft Knowledge base and research this problem.
In the mean time if someone can help, it is much appreciated.
Running MSSQL 2000 on Windows2000 in an NT domain. We have several jobs created by the web assistant wizard. They run along just fine for days or weeks, then all of a sudden SOME of them (?!) begin to fail with the message "Unable to perform a SETUSER to the requested username 'dbo' because the username is invalid for database 'xxx'" where xxx is of course the db where the proc resides. SQLAgent service is running as a domain admin and has not changed; job is owned by sa and has not changed; the domain admin in question is in sa role and has not changed. Something sure has changed, but what? Sometimes when this happens we can clear it up by deleting the web job and simply re-creating it. Today we have three culprits and none of our usual tricks are working. Why does this happen? How can we fix it? How can we PREVENT it! Thanks for any words of wisdom out there.
After observing brute force attacks on our VPS myhosting instance against the SA login, I change the SA login name. Now my two new backup jobs are failing. The agent service logs in as NT ServiceSQLSERVERAGENT. Nothing changed there (so far as I know and I'm the only one who should be on the VPS). The job owner was SA and after changing the SA account that was reflected in the SSMS gui; job owner 'newsaname'. I'm sure I can just rebuild the maintenance plans but I'd like to know what happened.
Also, I would like to learn more about the brute force attacks and how to determine what port they are comming in on. I see an IP address associated with them. Does that mean they are coming in on 1433 or 1434?
SQL 2012 Standard VPS Windows 2012 Server Standard
After upgrading a server from SQL 2000 to SQL 2005, I can't get any SQL Agent jobs to run, giving logon failed messages. MSSQLSERVER and SQLSERVERAGENT services are set up to run as the same domain account which has administrative rights to the server and is a SQL login with the sysadmin role. For basic testing purposes, I created a test job that all it does is run a T-SQL command 'SELECT @@VERSION'. The job is also set to run as the same account that the Agent is running under. Below are the logs (with trace messages on) from the point of starting the Agent to my attempt to run the job (user and server names have been changed to protect the innocent):
2007-10-22 15:31:15 - ? [297] SQLServer Message: 15457, Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (DisableAgentXPs)
2007-10-22 15:31:15 - ? [297] SQLServer Message: 15457, Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (DisableAgentXPs)
2007-10-22 15:31:15 - ? [297] SQLServer Message: 15457, Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (DisableAgentXPs)
2007-10-22 15:31:15 - ? [100] Microsoft SQLServerAgent version 9.00.3042.00 (x86 unicode retail build) : Process ID 5168 2007-10-22 15:31:15 - ? [101] SQL Server TESTSERVER version 9.00.3042 (0 connection limit) 2007-10-22 15:31:15 - ? [102] SQL Server ODBC driver version 9.00.3042 2007-10-22 15:31:15 - ? [103] NetLib being used by driver is DBNETLIB.DLL; Local host server is 2007-10-22 15:31:15 - ? [310] 4 processor(s) and 3328 MB RAM detected 2007-10-22 15:31:15 - ? [339] Local computer is TESTSERVER running Windows NT 5.2 (3790) Service Pack 2 2007-10-22 15:31:15 - ? [431] Populating subsystems cache... 2007-10-22 15:31:15 - ? [432] There are 11 subsystems in the subsystems cache 2007-10-22 15:31:15 - ? [124] Subsystem 'TSQL' successfully loaded (maximum concurrency: 80) 2007-10-22 15:31:16 - ? [124] Subsystem 'ActiveScripting' successfully loaded (maximum concurrency: 40) 2007-10-22 15:31:16 - ? [124] Subsystem 'CmdExec' successfully loaded (maximum concurrency: 40) 2007-10-22 15:31:16 - ? [124] Subsystem 'Snapshot' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ? [124] Subsystem 'LogReader' successfully loaded (maximum concurrency: 100) 2007-10-22 15:31:16 - ? [124] Subsystem 'Distribution' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ? [124] Subsystem 'Merge' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ? [124] Subsystem 'QueueReader' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ? [124] Subsystem 'ANALYSISQUERY' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ? [124] Subsystem 'ANALYSISCOMMAND' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ? [124] Subsystem 'SSIS' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ? [129] SQLSERVERAGENT starting under Windows NT service control 2007-10-22 15:31:16 - + [260] Unable to start mail session (reason: No mail profile defined) 2007-10-22 15:31:16 - ? [174] Job scheduler engine started (maximum worker threads: 400) 2007-10-22 15:31:16 - ? [193] Alert engine started (using Eventlog Events) 2007-10-22 15:31:16 - ? [146] Request servicer engine started 2007-10-22 15:31:16 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect 2007-10-22 15:31:16 - ? [110] Starting SQLServerAgent Monitor using '' as the notification recipient... 2007-10-22 15:31:16 - ? [133] Support engine started 2007-10-22 15:31:16 - ? [167] Populating job cache... 2007-10-22 15:31:16 - ? [297] SQLServer Message: 0, SQLServerAgent Monitor started successfully. [SQLSTATE 01000] 2007-10-22 15:31:16 - ? [168] There are 2 job(s) [0 disabled] in the job cache 2007-10-22 15:31:16 - ? [170] Populating alert cache... 2007-10-22 15:31:16 - ? [171] There are 0 alert(s) in the alert cache 2007-10-22 15:32:59 - ? [177] Job Test1 has been requested to run by User DOMAINmyaccount 2007-10-22 15:32:59 - ! [382] Logon to server 'TESTSERVER' failed (ConnAttemptCachableOp) 2007-10-22 15:32:59 - ! [382] Logon to server 'TESTSERVER' failed (ConnAttemptCachableOp) 2007-10-22 15:32:59 - ! [382] Logon to server 'TESTSERVER' failed (ConnAttemptCachableOp) 2007-10-22 15:32:59 - ? [184] Job completion for Test1 is being logged to sysjobhistory 2007-10-22 15:32:59 - ! [382] Logon to server 'TESTSERVER' failed (ConnAttemptCachableOp)
I recently changed the SA password on my SQL Server 6.5 installation and discovered that this had caused the Scheduled CmdExec Tasks (defined in SQL Executive) to fail with:
"Process Exit Code 1. Microsoft (R) SQLMaint Utility, Version 6.50.240 Copyright (C) Microsoft Corporation, 1995 - 1996 [Microsoft SQL-DMO] Error 4002: [SQL Server] Login failed"
Changing the password back to the original resolves the problem, but is a less than satisfactory solution.
Any advice on how I can change the SA password and allow CmdExec tasks to continue running would be gratefully appreciated.
Hi,In case of failover to standby server, what is theest way to redirectclient applications to new server?1) DNS name change -- not viable due to Caching issue.IS there any alternate like Oracle's onames/LDAP for resolving namewith sql server?Can use Sever alias?Thanks
I have couple of paramters with operator 'range(inclusive)' in my report(Agains datacube), and I always get failure when I choose value with them. here's a example( 3 such parameters on my report).
...
From date To Date
...
From age To Age
...
From Salary To Salary
@salary is the last one.
when i choose a value for 'From Salary' or 'To Salary' from the dropdown list, it runs correctly. But if I choose value for 'from age' or 'To age' or 'From Date' or 'To Date', it fails due to
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'DimTimesheetTimesheetNumber'. (rsErrorExecutingCommand)
Range operator ( : ) operands have different levels; they must be the same.
BTW, all the default values for these parameters are 'all', since it allows only 'all' to select when I set up the parameters, and I tried to change the default value, but it caused errors about the parameter dependency.
I am trying to write a T-SQL script to change about 200 SQL Agent jobs to send notification to a different email notification list. I could do this manually but it would take me a lot more time. Is there a way to script this out in T-SQL? Thanks!
I have one server out of 6 that lets me logon as SA with the default password of null. If I run this command to change the password I get password changed, but it did not change it, I still can logon as SA with the NULL password.
The old administrator left and I am learning slowly. Since his password was changed, SQL cannot start. I get an error 1069 login failed. Maybe you have a good idead I haven't thought of?
I've searched this forum and was surprised to not find this asked before - Is there any way to change SA's password (we have managed to lose it somehow)? And we do have physical access to our SQL Server of course...if that helps.
Dear experts, i'm getting problem with sql server 2000. actually i changed the service from automatic to manual, right from that day,sql server is not working on my machine.when i checkd in the properties of sql server registration, it is showing some around 8 letter password. actually i will connect with sa and password sa.
where should i change the password? is there any other solution?
or is it better to uninstall and install again? if so what about my databases?
ikram writes "i have to changed the password of sql server and after password change it is not assassable form the network. some of my friend tell me that you will have to change some configuration form server side file. he dont know that file exactly. please tell me the soloution.
I am newbie at SQL server, we have sql 7.0, sql2000 and sql2005. we need to change SA password but developers claim they have hardcode the SA password.
anyone can tell me where developers hardcode this SA password? what are the files i need to look up? if they hardcode it, impossible to change?
I have a SQL JOB that I inherited that uses FTP to xmit some data. When I attempt to change the password within the FTP job a window warns me that files will be deleted. My question is are these files just sytem buffers or will any important files get the axe ? Thx in advance. Lou
I am trying to change password, using the code below.. The code below is in a stored proc and I am passing these parameters from a web application..... sp_password [ [ @old = ] 'old_password' , ] { [ @new =] 'new_password' } [ , [ @loginame = ] 'login' ]
sp_password 'cust1','cust2','cust1' Server: Msg 15210, Level 16, State 1, Procedure sp_password, Line 20Only members of the sysadmin role can use the loginame option. The password was not changed. I made my user part of the db_securityadmin role, but that does not help... How can I give the above mentioned permissions, so that when user cust1 logs on then he can chnage his own password from the web application as long he has given his old password correctly?
When using osql from a command prompt the following script was run: EXEC sp_password 'Current Password', '', 'TestLogin'
We were trying to set a password for a login to blank, but now cannot access login into the application because the application was looking a blank password for this user but the password would not work for blank.
Note ---This client only has MSDE installed, not SQL Enterprise.
If the script is run from Query Analyzer it works fine.
The script should have been this: EXEC sp_password 'Current Password', Null, 'TestLogin'
It should have explictly stated NULL for the new password but it wasn't done.
Is there anyway to reset the password without knowing the existing password. No other logins exist. They tried logging into the system with no password, '',"". I am not sure what the password was set to.
Howdy - I am using SQL 7.0. I have done some research using the Books On Line and I do not find any documentation on how to change the sa password using a SQL Script. Can anyone point me in the right direction?
I newly installed the SQL Server 2005.During installation i chose the option Mixed Mode.I aslo did nit mention any user name and password to the SQL Server.Iam able successfully create Databases using the SQL Server Management Studio. My problem iam unable to connec to the SQL Server from my .net windows application.My application code is
Dim strCon As String Dim sValue As String Dim strQuery As String Dim dsEventTransac As New DataSet Dim daEventTransac As New SqlClient.SqlDataAdapter Dim rowCount As New Integer Dim oWebService As New WebReference.TkmService 'Dim nwRow As DataRow 'Dim delCmd As SqlClient.SqlCommand
strQuery = "SELECT * FROM EventTransaction"
strCon = "Data Source=localhost;integrated security=SSPI;initial catalog=KmsDb;User ID=sa;password=" objCon = New SqlClient.SqlConnection(strCon) If objCon.State = ConnectionState.Closed Then objCon.Open() End If daEventTransac = New SqlClient.SqlDataAdapter(strQuery, objCon) Dim cmdBuilder As New SqlClient.SqlCommandBuilder(daEventTransac) daEventTransac.Fill(dsEventTransac)
'update the DataAdapter daEventTransac.Update(dsEventTransac) oWebService.Url = "http://192.168.0.10/TkmService1/TkmService.asmx"
''display the table in a grid dgEventTransac.DataSource = dsEventTransac.Tables(0)
Catch ex As Exception MessageBox.Show(ex.Message) End Try End Sub
Private Sub DataGrid1_Navigate(ByVal sender As System.Object, ByVal ne As System.Windows.Forms.NavigateEventArgs) Handles dgEventTransac.Navigate
End Sub End Class
When i donot specify user ID and password i get connected correctly.But when i run the above code i get an error "Unable to connect to the remote server" Now i want to change the username and password.Please tell me how to do
I newly installed SQL Server 2005 .I did not mention any username and password during installation.But now i want to specify one.Can you tell me how to do so .My Operating System is WindowsXp.
Theres a script that the SQL workprogram instructs someone to run which shows the last change date for a users password. Within the results of the script theres two rows of dates and I would like to know which date indicates the "last password change date"
