Wjen sql 2005 is installed a Service Master key is generated using the password of the account under which sql 2005 server runs.
Suppose I use a domain account to run sql server. The account password will change every so often. I presume this change will not impact the validity of the existing Service master key and therefore any data indirectly encrypted by it. Am I correct?
Barkingdog
Folks,
MSSQLServer and SQL Server Agent services under NT are running under a system account under our domain (setup many moons ago) for which we have lost the passsword. Is there any way we can recover these passwords?
Thanks.
Sam
We have changed NT Administration Password. Now how to reassign the new password setting for sql server service account. As right now all schedule jobs are getting failed & needs to be executed manually.
Thanks in Advance
Manoj
Ugh! Someone changed the password of our SQL Server service account. It is called syssql, and it is used by the MSSQLServer and SQL Executive services to log in at startup. After the password was changed, we noticed that replication wasn’t running, and since I know that replication uses the Executive service, I restarted that service using the new password. That worked to get replication working again, and since the boxes were production machines I didn’t restart the MSSQLServer service with the new password yet. Now, our syssql account keeps on locking up every so often, and scheduled tasks that use xp_sendmail stopped working, and alerts stopped sending. Is this happening because of the MSSQLServer service still being logged in with the old password? I suspect that the SQL Mail logs in through the MSSQLServer service, which is still using the old password, and the login failures are causing the syssql account to lock. Any other ideas???
Cindy Rutherfurd
cindy.rutherfurd@zcsterling.com
We are running SQL Server 2000 on two servers and when they were built, the same domain account was used for all installations, and the MSSQLSERVER and SQLServerAgent services run logged in as that same account. That account is also the dbo of all the SQL databases. We now need to change the password and possibly disable that domain account.
What do we need to do to make sure the SQL Servers and databases continue to run without problems after making the password change and/or disabling the account?
I appreciate any advice!
I noticed when I restore a master database to a server other then the one which created the backup of master, SQL Server contains the following three local security groups that were defined on the source server. The problem is these groups are "local" and do not apply to the server where master was restored.
ServerNameSQLServer2005MSFTEUser$ServerName$InstanceName
ServerNameSQLServer2005MSSQLUser$ServerName$InstanceName
ServerNameSQLServer2005SQLAgentUser$ServerName$InstanceName
For example, if you have a default SQL Server instance named MARKETING_TEST the security folder will contain the following three entries.
MARKETING_TESTSQLServer2005MSFTEUser$MARKETING_TEST$MSSQLSERVER
MARKETING_TESTSQLServer2005MSSQLUser$MARKETING_TEST$MSSQLSERVER
MARKETING_TESTSQLServer2005SQLAgentUser$MARKETING_TEST$MSSQLSERVER
If you then backup the master database on an instance named MARKETING_PROD and restore it to MARKETING_TEST, the security folder on MARKETING _TEST will now contain the following three entries.
MARKETING_PRODSQLServer2005MSFTEUser$MARKETING_PROD$MSSQLSERVER
MARKETING_PRODSQLServer2005MSSQLUser$MARKETING_PROD$MSSQLSERVER
MARKETING_PRODSQLServer2005SQLAgentUser$MARKETING_PROD$MSSQLSERVER
These entries would be invalid because no such server exists and therefor no such local groups exists. There appears to be no Microsoft documentation explaining how to handle these groups when restoring master from one server to another. My assumption is that whenever restoring master to another server you must drop these three groups and add the correct corresponding groups along with the appropriate permissions. I don't understand why SQL Server would not rebuild this information for you during a restore.
Any explanations?
Dave
During install of SQL Server 2005, we can of course use a domain account or the built-in system account for running the services. I lean toward domain for obvious reaons but would like to know a +/- to each option and why I'd choose one over the other and what consequences or limitations one may encounter if I choose one over the other.
View 6 Replies View RelatedI have several DTS jobs that runs well as a job with my nt login account for the SQL agent service startup account, but if I use the System account
they fail with this error.
" Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider"
The data has change access to the System account under the NT security.
Thank you in advanced.
Jorge
Hello! I have the following problem. I developed CLR Stored Procedure "StartNotification" and deploy it on db. This sp calls external web service. Furthermore, this sp is called according with SQL Server Agent Job's schedule. On my PC SQL Server works under Local System account and this web service is called correctly (Executed as user: NT AUTHORITYSYSTEM). But on ther other server the following exception is raised during job running:
Date 17.04.2007 16:42:10
Log Job History (FailureNotificationJob)
Step ID 1
Server MSK-CDBPO-01
Job Name FailureNotificationJob
Step Name MainStep
Duration 00:00:00
Sql Severity 16
Sql Message ID 6522
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: CORPmssqlserver.
A .NET Framework error occurred during execution
of user defined routine or aggregate 'StartNotification':
System.Security.SecurityException: Request for the permission of type
'System.Net.WebPermission, System, Version=2.0.0.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089' failed. System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check(Object demand,
StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.CodeAccessPermission.Demand()
at System.Net. The step failed.
What is the reason of this behaviour? Unfortunately I do not have direct access to this server.
I have the following guesses:
1) CORPmssqlserver may have not enough permissions to call web service
2) Something wrong with SQL Server account's permissions
2) Something wrong with SQL Server Agent account's permissions
I will take the will for the deed. Thanks.
Hi all,
I do understand that it is highly recomended to have aserprate user (perfered a domain user account) for each of the SQL Server service and SQL Agent service.
What is the reason behind that? (Someone told me to not run the service with an account that has a powerul privilegs! - I don't undrstanmd this point can you explain it please?)
What is the diffrent between: 1- Local System account 2 -Network Service account
Thanks in advanced!
CS4Ever
Well, I inherited a SQL Server ....sob story.........installer won`t call back.....sob story.....buying books like mad......sob story. Geez, this was a *great* job when I interviewed.....
Regardless, I do not know the password for the sa account on a SQL 6.5 server running on NT4SP3. We are using standard securtiy. Do I have any options?
Hopefully, the technical discussion doesn`t reference rubber garden watering implements. :{)
Thanks,
steve
Microsoft recommends that you do not use the Network Service account to run the SQL Server service (see http://msdn2.microsoft.com/en-us/library/ms143504.aspx).
Can anyone tell me what the drawbacks are of doing this?
Okay now this is weird, today the Reporting Services was not running and here are the entries in the event log:
Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7041
Date: 12/12/2007
Time: 9:47:22
User: N/A
Computer: TFS
Description:
The ReportServer service was unable to log on as DOMAINTFSREPORTS with the currently configured password due to the following error:
Logon failure: the user has not been granted the requested logon type at this computer.
Service: ReportServer
Domain and account: DOMAINTFSREPORTS
This service account does not have the necessary user right "Log on as a service."
User Action
Assign "Log on as a service" to the service account on this computer. You can use Local Security Settings (Secpol.msc) to do this. If this computer is a node in a cluster, check that this user right is assigned to the Cluster service account on all nodes in the cluster.
If you have already assigned this user right to the service account, and the user right appears to be removed, a Group Policy object associated with this node might be removing the right. Check with your domain administrator to find out if this is happening.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp
I am the administrator of the machines and I can assure you that no domain policy has changed for a couple of weeks. What should I look for?
Hai,
While installing SQL Server 2000 on my Windows 2000 Advanced Server, i choose Windows Authentication mode, so i was not able to enter a password for sa account. And after installation, in Enterprise Manager i could still see sa account under logins but the account has a password.
So my question is what is the default password for sa account when we choose Windows Authentication mode and is it possible to change the mode from windows Authentication to Mixed Authentication mode after installation, with out re-installaing SQL Server 2000.
Thank you in advance
With regards
Sudar
I am trying to adapt a vb6 application to support sqls 2005 features. I am using SQL Server Authentication and want to support the feature which forces the user to change his password on the next login. Using an SQLOLEDB connection string, on trying to log I get an error saying that the password must be changed - fair enough - but how can I do this? I would have expected to be able to supply both old and new passwords in the connection string, but there does not seem to be any such feature. Am I missing something obvious?
Couldn't able to login with sa account, seems someone having admin account have changed this sa password.Is there any way to determine who and when have changed this password.It is known that SQL can't log this information in its log, is there any other ways.
View 7 Replies View RelatedIf someone can tell if it is wise change the SA account password after all of your databases have been set up using NT Authentication for login. Also, by using the sa password at login are you providing more security and and who should have access to that password (Your developers or your Administrators?)
Thanks
HI,
I m swagatika and working on a s/w company.I am facing some problem in MS SqlServer2005.
my problem is:-
i installed sql server2005 completely .At the install time , i set the username='xyz' and password=blank (windows authentication mode).
Now i want to change my user account to 'sa' and password to blank.
After installation,how to change the user account and password.
Please can anybody help me ?
Hi
I installed SQL Server 2005 Express Edition. When I try changing the account name, password in the SQL Server Configuration Manager, i.e. by clicking on Apply, the SQLEXPRESS restarts and the password gets replaced by a longer password. Also the user name gets prefixed with "./". Any help on this will be highly appreciated.
Another query: Do we have the query analyser (gui or command line) kind of thing in Express Edition? Also where can I get a proper documentation of doing elementary things in setting up a database, like creating a database, adding a user, etc)
Thanks and Regards
Roopesh
A former Network Manager setup the Sql Server Services NT Domain account. I need the password to set it up on our MS Exchange server, should I change the password or create a new domain account.
How can I find out if there is no other applications using the service account in our domain, since the new Network manager doesn't know the password.
What happens if I change the password.
Hi everyone -
Is there a way to display the current password for a user account
on SQL server 2000???
thanks
tony
I can get to the DB and find the user account, but I see nowhere to change the password? My app requires a password that meets Windows Security requirements and the one that was initially created is not long enough.....
View 1 Replies View RelatedWe are implementing PCI (Payment Card Industry) and one thing that is required is that we change our encryption keys once a year or when a key custodian leaves. The only way that I have seen to do that is to decrypt all the encrypted data, drop and recreate the symmetric key, the certificate, and database master key, and then re-encrypt the data.
Is there any way to just change the password on the database master key? I understand that if the actual symmetric keys have been breached, that would not be enough, but for routine maintenance, just changing the password on the database master key would seem to be enough.
I have looked and looked, but have not found anyone else that is doing this type of thing. Maybe I am just not looking in the right places. Can anyone offer some guidance? Thank!!!
Jim Youmans
hi, all:
I installed a Sql 2000 Personal Edition in my Laptop,now I want to change my Sql 2000 sa account password.
but I can not find where I can get the default password for sa account and how to change it .
thanks
the password of sa account is empt
I use "sqlcmd -S servername -U sa " command but failed
any suggestions?
thanks
Hi All,
I have a sql server database user with Password must change, and I get this error when i use ODBC connection wizard,
18488
Login failed for user '%.*ls'. Reason: The password of the account must be changed.
where would the windows shows up to change it similary we do when we connect through Management studio and provide new password.
Any idea.
Any idea why this happened and what do i have to do to reslove it?
Code Snippet
Login failed for user 'dd_user'. Reason: The password of the account must be changed.
How can I find account that the SQL Server service is using ?
Plz help.
hi.. i do not know which to choose when my installation comes to the service account page ..
should i use the local system or write the domain user account ?
i use domain user account .. but what is my domain ?
Hi,
I am trying to set properties on a SQL Server7, but when I get to the tab for 'Startup Service account', it is greyed out. Also, the same for properties for SQL Server Agent.
Why can't I change it?
To schedule jobs, and have SQL mail, don't I need to set up a Startup Service Account?
Thanks for your help,
Judith
Has anyone ever converted from running SQL Server under the Local System account to running under a Domain User account?
I have often installed SQL using a Domain User account, but I am inheriting a couple of SQL Servers that were set up to run under Local System. I have never had to convert "on the fly" before.
If you have any input or insights, I would be grateful.
Regards,
hmscott
I just set up a SQL 2005 Server about a month ago that we will be moving all of our scattered DBs onto. I basically set it up with the default settings and didn't touch anything special, until I tried to install Microsoft System Center Essentials 2007 in our environment. I had problems getting it to use our SQL server, and a forum post told me to change all of the service accounts for SQL to use the LocalSystem login. So here are my service accounts:
SQL Server Integration Services
- NT AUTHORITYNetworkService
SQL Server FullText Search (MSSQLSERVER)
- LocalSystem
SQL Server (MSSQLSERVER)
- LocalSystem
SQL Server Analysis Services (MSSQLSERVER)
- LocalSystem
SQL Server Reporting Services (MSSQLSERVER)
- LocalSystem
SQL Server Browser
- LocalSystem
SQL Server Agent (MSSQLSERVER)
- LocalSystem
So Sandisk makes this software called CMC. It's for controlling their enterprise USB drives. And their software won't install. It errors out saying that it couldn't drop the database on our SQL server (but it doesn't exist). If I make an empty DB by the same name, it sees it, and then errors out anyway. I am using the SA login for testing (I was using a purposed SQL account before) so I don't think it's a rights issue. Sandisk says it should work, and they suggested I use SQL server express. But we run VMs, and running SQL server in another VM is going to use more of our memory pool. Plus we want centralized backups and all that.
Do my service account logins have anything to do with it? Can someone tell me what these should be set to by default so I can change them back?
Here's a trace I did when I tried to install the software:
-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
set implicit_transactions on
go
drop database [CruzerDb]
go
IF @@TRANCOUNT > 0 ROLLBACK TRAN
go
And here's more info if needed:
Product Version
- 9.00.3042.00
Edition
- Standard Edition
Server Collation
- SQL_Latin1_General_CP1_CI_AS
Is Clustered
- No
Is FullText Installed
- Yes
Is Integrated Security Only
- No
Is AWE Enabled
- No
# Processors (used by instance)
- 2
SqlServer2k is on the domain serverSqlServer2k is on a laptop tooI want to copy a database from the domain to the laptop over the networkusing the copy database wizard.I have done this before with no problem but this time I get thefollowing error:Your SQL Server Service is running under the local system account. Youneed to change your SQL Server Service account to have the rights tocopy files over the network.I went into the properties of MSSQLSERVER under Services andApplications and see no setting described.Where do manage the SQL Server Service?*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 3 Replies View Related