June 11, 2010 at 8:42 am
Env: Windows server 2003, SQL Server 2005 sp3
@@version=Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) May 26 2009 14:24:20 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
(sp3 was applied about a year ago, though above shows as sp2 for @@version)
Oracle 10g
Following info from doing About in Management stuido:
Microsoft SQL Server Management Studio 9.00.4035.00
Microsoft Analysis Services Client Tools 2005.090.4035.00
Microsoft Data Access Components (MDAC)2000.085.1132.00 (xpsp.080413-0852)
Microsoft MSXML2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer7.0.5730.11
Microsoft .NET Framework2.0.50727.832
Operating System5.1.2600
----------------------------------------------------------------------
It was running like a charm with linked server setup for Oracle using Oracle Provider for OLE DB (OraOLEDB.Oracle) for more than a year till June 1st of this year.
Long ago I had removed Builtin/Administrators from sysadmin persmission, it has been working fine all along.
There have been no changes to Oracle this year.
They decided to implement password change enforcement every six months in windows, which kicked in on June 1st. Operations put in changes to enforce password changes.
When stored procedures are executed from MS SQL Server Studio, SQL Server crashes sometimes, have to restart, then it will work till next crash. Varying messages show up, some related to the OLE DB drvier, and other times, access violations, recent message
"Either no algorithm has been specified or the bitlength and the algorithm specified for the key are not available in this installation of Windows".
At one point SQL Server was running on the server, but could not connect from my desktop using SQL Server Management stuido, Following message showed up:
"Server TCP provider has stopped listening on port [ 1182 ] due to a failure. Error: 0x2736, state: 1. The server will automatically attempt to reestablish listening."
We had to reboot the server, then I was able to connect to SQL Server.
I also use Report server, as long as SQL Server did not crash, reports work fine (they use SQL Server stored procedures that use also oracle via linked server).
It seems like when queires are submitted via Management Studio that uses the linked server, it crashes SQL Server or causes strange messages to appear.
I don't belive I have applied any hot fixes since SP3, is there a way to tell if I have applied any hot fixes since sp3. Should I apply cumulative update #9 to sp3.
Any ideas would be greatly appreciated.
June 11, 2010 at 9:03 am
Can't help with the crashes, but I did want to respond about the SP 2 comment.
In the information returned by @ @VERSION, SP 2 refers to the OS, not SQL Server 2005. The build number for SQL Server 2005 is SP 3.
June 11, 2010 at 11:50 am
samiam914 (6/11/2010)
They decided to implement password change enforcement every six months in windows, which kicked in on June 1st. Operations put in changes to enforce password changes.
Do these password changes affect the SQL Server service accounts?
That might be part of your problem with the crashes. Too many things in SQL Server rely on domain accounts and when the account password gets changed, not everything always is "aware" of the change.
Did you check your Event Viewer logs (Security especially) in addition to the SQL Server logs?
June 11, 2010 at 1:12 pm
Hi Brandie,
Thank You. I was told that service accts have non expiring passwords (hopefully they were always that way), we never had in the past password change enforcement, this is the first time.
I did look in the event log, lot of those messages state SQL Server terminated and recovering databases; sometimes we have to start the services manually or reboot the server. Strangely same procedures work some times and at other times they crash the SQL Server.
In configuration manager, all services show "Log on as=Local system", except IIS shows as 'NT Authority Network services". Server is started by ID that supposedly has password with no expiration.
My ID requires a password change, it is also local admin on the server; can I restart any SQL Server services with my ID, in the even of a crash without rebooting the machine.
Long ago I had removed Builtin/Administrators from sysadmin persmission.
How do we make all SQL Services recognize the password change.
I greatly appreciate your help.
June 14, 2010 at 4:44 am
It's not services you necessarily have to worry about. It's all the little things, like Linked Servers, jobs or SSIS packages that specifically call those accounts, etc.
The only way to do it is to go through your entire set of servers looking for anything that references those accounts and inputs a password. Make sure the passwords are all changed on that level.
But first, did you just go through the system log or did you check Security and Application too? Security should tell you about failing accounts. Application might have other information, not related to SQL Server or specifically related to SQL Server, in it that would help you track down the issue.
June 14, 2010 at 8:11 am
I am having trouble with linked server. In this linked server, only one windows ID is mapped to an Oracle ID (oracle id is only in oracle DB not related to windows, it's password does not conform to windows check password policy), I use OraOledb provider.
All IDs that access the linked server use the remote login using Oracle ID, that is how I had setup in the linked server security tab(used option "Be made with this security context").
when a stored procedure accesses oracle tables using linked server, it works some times, other times it crashes sql server. Some times I get message "Either no algorithm has been specified or the bitlength and the algorithm specified for the key are not available in this installation of Windows" other times linked server related messages like 'Cannot get properties from OLE DB provider "OraOLEDB.Oracle" for linked server'.
When accessing strictly SQL Server tables only, it seems to work fine.
I did have a credential, I changed passwords on it, the jobs are running without problems, all back ups are also running without problems. Reports are working fine, they use procedures that use oracle linked server.
So far it is only when I execute stored pricedures that use oracle linked server from Management studio using my ID, sometimes it crashes sql server. My ID is sysadmin, and I had to change my password.
I changed passwords for all windows IDs yesterday (by logging into windows), including the one that is used for linked server.
All ID's are windows authenticated, except for the sa ID.
Scripting of the sa id shows:
CREATE LOGIN [sa] WITH PASSWORD=N'...', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
EXEC sys.sp_addsrvrolemember @loginame = N'sa', @rolename = N'sysadmin'
sa is the dbo of master, msdb, modeldb & tempdb.
I left sa password as is, did not change. sa ID is not used by anyone, i was created during installation, I left that alone, I have other windows ids that have sysadmin authority.
Is there another password for linked server somewhere, I only know of remote login and password, in my case they are oracle ID and password, which I left them alone. As I said before, I did change the password for windows ID associated with this oracle ID.
Thank you for your help.
PS. My ID is the owner of the database, with my windows password changing, do I need to make any changes.
June 15, 2010 at 2:54 am
One possible scenario: Somewhere in your system the password of a domain account is saved, and a process is trying to do work using this account and password. The password is either expired or has been changed and the process cannot handle the messaging given by Windows in this situation.
It may be worth working with your Windows Admin people to identify any accounts with an expired password, and see if one of these is used in your system.
If the password has been changed, then your process using the old password is likely to get the account locked out. So again work with your system admin people to identify locked out accounts that you think may be associated with SQL Server.
In many installations, the accounts used for the xp_cmdshell proxy and the SQL Agent proxies will be normal domain accounts, and for all of these SQL Server stores the password it will use. You may find there are similar situations within Oracle. If you have any of these, you will need to re-register the account in SQL (or Oracle) with the new password, and put a process in place for dealing with or preventing password changes.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 15, 2010 at 11:14 pm
Thank You. Can you please point me to where the SQL Server stores the password for xp_cmdshell proxy and the SQL Agent proxies you mentioned, so that I can change them.
I did change the password of the ID that is associated with the credential.
June 16, 2010 at 2:35 am
You do this via Management Studio.
For xp_cmdshell, right-click on your instance name in the Object Explorer window and select Properties. You specify the xp_cmdshell settings on the Security tab.
For job proxies, expand SQL Server Agent, then expand Proxies. You can then find if any proxies have been set up.
If you need more help in changing proxies, please look at Books Online before asking a question on this forum. You will probably find the answer, and get it much sooner.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 16, 2010 at 6:29 am
samiam914 (6/14/2010)
I am having trouble with linked server. In this linked server, only one windows ID is mapped to an Oracle ID ... it's password does not conform to windows check password policy), I use OraOledb provider.
What sort of trouble is this causing you? Are you unable to save the password in the linked server?
EDIT: If I'd read your post more carefully, I'd have noticed you apparently were able to save the linked password. Sorry about that.
Allow me to rephrase... Your main problem seems to be with the Oracle Linked Server. Are you able to change the password on the Oracle end to conform with Window password standards? Does that assist with the crashing problem?
July 1, 2010 at 11:50 am
It appears to be fixed now, still don't know exactly what caused the problem, and how it really got fixed. Here are the three things that were done, after that it looks good of so far.
1) IDs had a weak password (windows authenticated), changed to suit to conform to new password rules. couple of IDs were sysadmins.
2) Applied windows 2003 server patches. Before the patches, was getting several errors, mostly related to security and some related to linked server. After applying patches, narrowed down to couple of error messages.
3) I had to go back to the stored procedures that were causing the crash, and rewrite every line in a new file (i.e. I typed every word in a new file), did not copy and paste, started fresh.
I had this problem before somehow invisible characters were getting embedded into the code, in the past also it crashed SQL Server, but consistently in the same place with same error, this time it was crashing randomly with different types of messages.
So far it looks good. Thanks for your help.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply