Instead of shrinking it, back it up first, this should get
rid of the active parts of the log file, then shrink it.
Peter
>--Original Message--
>Hi,
>A SQL Server2000 database we have has ended up with a
>massive Transaction Log (basically because the
maintenance
>schedule needs looking at).
>Anyway to cut a long story short, I backed up the
>transaction log which truncated it, but I can't seem to
>shrink the logfile to free up the space.
>I tried:
>DBCC SHRINKFILE (2, 200, TRUNCATEONLY)
>[where 2 is the filenumber of the logfile]
>This came back with:
>'Cannot shrink log file 2 (Main_Log) because all logical
>log files are in use.'
>Can anyone tell me how I can shrink this log file please?
>Regards,
>Vince
>.
>Check this article out
Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://support.microsoft.com/?id=272318
Regards,
Gayathri Rau [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
Showing posts with label active. Show all posts
Showing posts with label active. Show all posts
Sunday, March 25, 2012
Can't setup replication: Encryption error using CryptProtectData?
I am trying to setup replication but am having the following issues
Publisher:
SQL 2005 SP1 Enterprise Cluster (Active, Passive)
Subscriber:
SQL 2005 SP1 Enterprise
When trying to create a publication or subsubscription running under a Windows Domain account the following error message appears:
Replication-Replication Distribution Subsystem: agent ECHO\ECHO-InsDB-INS_CMS_PUB-HARPO-58 failed. Unable to start execution of step 2 (reason: Error authenticating proxy DETINI\srvRep_user, system error: Logon failure: unknown user name or bad password.). The step failed.
Further in the SQL logs we find that the above message is due to:
[298] SQLServer Error: 22046, Encryption error using CryptProtectData. [SQLSTATE 42000]
It appears that when Credentials for the agent proxy are being created that this message occurs. So when the SP that tries to run the agent (snapshot or distrobution) is called it always returns incorrect password details.
The Credentials do appear to be created, they are visible via SQL Studio, but you can't see the password. Changing the password via SQL Studio does not work as the Credentials are recreated every time.
I have checked the following:
MSDTC is running correctly
The Domain Account has the correct privileges to run these services
Regenerated the Service Master Key
Created Master Keys in each database effected by replication
Ensure SQL Service accounts have access to decrypt the Service Master Key
I can create a Publication and Subscription using the local system account on the cluster
Does anyone have any idea ?
Chris
Chirs,
My testing environment is the same as yours - the publisher/distributer is clustered SQL2005 enterprise with SP1 on windows 2003, and the subscriber is SQL2005 enterprise on 2003 server, and I have got the same error when I was trying to run the creating snapshot job. The only workaround I found was to put the distributor on the same server as the publisher, and use a local directory for shapshot folder, which was not ideal, but at least I could continue with my test. Then I had the same error again when I ran the push distribution job using a domain ammount. I had to change the security to use SQL agent account, which is a domain admin account.
I would like to know how to avoid the error too, since I want the distributor to be on a different machine than the publisher, and limit the rights of the credentials on the production environments.
Ying
sqlCan't setup replication: Encryption error using CryptProtectData?
I am trying to setup replication but am having the following issues
Publisher:
SQL 2005 SP1 Enterprise Cluster (Active, Passive)
Subscriber:
SQL 2005 SP1 Enterprise
When trying to create a publication or subsubscription running under a Windows Domain account the following error message appears:
Replication-Replication Distribution Subsystem: agent ECHO\ECHO-InsDB-INS_CMS_PUB-HARPO-58 failed. Unable to start execution of step 2 (reason: Error authenticating proxy DETINI\srvRep_user, system error: Logon failure: unknown user name or bad password.). The step failed.
Further in the SQL logs we find that the above message is due to:
[298] SQLServer Error: 22046, Encryption error using CryptProtectData. [SQLSTATE 42000]
It appears that when Credentials for the agent proxy are being created that this message occurs. So when the SP that tries to run the agent (snapshot or distrobution) is called it always returns incorrect password details.
The Credentials do appear to be created, they are visible via SQL Studio, but you can't see the password. Changing the password via SQL Studio does not work as the Credentials are recreated every time.
I have checked the following:
MSDTC is running correctly
The Domain Account has the correct privileges to run these services
Regenerated the Service Master Key
Created Master Keys in each database effected by replication
Ensure SQL Service accounts have access to decrypt the Service Master Key
I can create a Publication and Subscription using the local system account on the cluster
Does anyone have any idea ?
Chris
Chirs,
My testing environment is the same as yours - the publisher/distributer is clustered SQL2005 enterprise with SP1 on windows 2003, and the subscriber is SQL2005 enterprise on 2003 server, and I have got the same error when I was trying to run the creating snapshot job. The only workaround I found was to put the distributor on the same server as the publisher, and use a local directory for shapshot folder, which was not ideal, but at least I could continue with my test. Then I had the same error again when I ran the push distribution job using a domain ammount. I had to change the security to use SQL agent account, which is a domain admin account.
I would like to know how to avoid the error too, since I want the distributor to be on a different machine than the publisher, and limit the rights of the credentials on the production environments.
Ying
Sunday, March 11, 2012
Can't run LDAP Query From Remote Machine
Hi all,
I have a SQL 2005 server with a linked server which points to our
active directory. I am able to query the active directory from the
local machine when RDC'ed into the server, but when I run the query
from a remote machine using Management Studio, I get this error:
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT *
FROM 'LDAP://prudc/DC=<domain>,DC=com'
" against OLE DB provider "ADSDSOObject" for linked server "ADSI".
The query is:
SELECT *
FROM OPENQUERY( ADSI,
'SELECT *
FROM ''LDAP://prudc/DC=<domain>,DC=com''
'
)
(note I replaced our domain name with <domain> in the above query and
error message)
This issue isn't specific to the above query as I've tried many ldap
queries and they have all worked on the local machine but failed on the
remote machine.
I'm completely stomped on this and would greatly appreciate any help I
can get.
ThanksHi Jim
This was a previous post when someone had the same error
http://tinyurl.com/pjg7s I am not sure how much use it will be!
If the query works on the server then I would expect it to be ok, which
probably leaves permission/access as the main issue. Can you use VB script to
query the AD e.g. using the scripts from http://www.rlmueller.net/?
John
"Jim" wrote:
> Hi all,
> I have a SQL 2005 server with a linked server which points to our
> active directory. I am able to query the active directory from the
> local machine when RDC'ed into the server, but when I run the query
> from a remote machine using Management Studio, I get this error:
>
> Msg 7320, Level 16, State 2, Line 1
> Cannot execute the query "SELECT *
> FROM 'LDAP://prudc/DC=<domain>,DC=com'
> " against OLE DB provider "ADSDSOObject" for linked server "ADSI".
>
> The query is:
> SELECT *
> FROM OPENQUERY( ADSI,
> 'SELECT *
> FROM ''LDAP://prudc/DC=<domain>,DC=com''
> '
> )
> (note I replaced our domain name with <domain> in the above query and
> error message)
>
> This issue isn't specific to the above query as I've tried many ldap
> queries and they have all worked on the local machine but failed on the
> remote machine.
> I'm completely stomped on this and would greatly appreciate any help I
> can get.
> Thanks
>|||Thanks for the help but unfortunately, I've already looked at that post
and the issue is a bit different.
The issue I'm having seems to have something to do with running a query
from a remote machine. So if I run a query on our SQL Server box from
my local desktop machine, I get the error. Running the query directly
on the SQL Server box while RDCed into the machine works flawlessly.
I did try to run a .vbs script from my machine which was able to query
the active directory...thanks for the link =). This leads me to
believe that it has something to do with SQL server security
restricting queries run from remote machines. I ran the surface area
configuration utility and didn't really see anything that jumped out at
me...
Anyone have any ideas?|||Alright, I've figured out a fix..
The AD linked server that I originally created was set to login to AD
with the credentials of the current security context. I changed this
to log in with a specified login and it worked fine. Whats strange is
that I set it to my own login account which I was using to run the
query remotely anyways. I guess SQL server queries ran remotely are
not run under the logged in users' security context after all?
Thanks for your help John =).
I have a SQL 2005 server with a linked server which points to our
active directory. I am able to query the active directory from the
local machine when RDC'ed into the server, but when I run the query
from a remote machine using Management Studio, I get this error:
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT *
FROM 'LDAP://prudc/DC=<domain>,DC=com'
" against OLE DB provider "ADSDSOObject" for linked server "ADSI".
The query is:
SELECT *
FROM OPENQUERY( ADSI,
'SELECT *
FROM ''LDAP://prudc/DC=<domain>,DC=com''
'
)
(note I replaced our domain name with <domain> in the above query and
error message)
This issue isn't specific to the above query as I've tried many ldap
queries and they have all worked on the local machine but failed on the
remote machine.
I'm completely stomped on this and would greatly appreciate any help I
can get.
ThanksHi Jim
This was a previous post when someone had the same error
http://tinyurl.com/pjg7s I am not sure how much use it will be!
If the query works on the server then I would expect it to be ok, which
probably leaves permission/access as the main issue. Can you use VB script to
query the AD e.g. using the scripts from http://www.rlmueller.net/?
John
"Jim" wrote:
> Hi all,
> I have a SQL 2005 server with a linked server which points to our
> active directory. I am able to query the active directory from the
> local machine when RDC'ed into the server, but when I run the query
> from a remote machine using Management Studio, I get this error:
>
> Msg 7320, Level 16, State 2, Line 1
> Cannot execute the query "SELECT *
> FROM 'LDAP://prudc/DC=<domain>,DC=com'
> " against OLE DB provider "ADSDSOObject" for linked server "ADSI".
>
> The query is:
> SELECT *
> FROM OPENQUERY( ADSI,
> 'SELECT *
> FROM ''LDAP://prudc/DC=<domain>,DC=com''
> '
> )
> (note I replaced our domain name with <domain> in the above query and
> error message)
>
> This issue isn't specific to the above query as I've tried many ldap
> queries and they have all worked on the local machine but failed on the
> remote machine.
> I'm completely stomped on this and would greatly appreciate any help I
> can get.
> Thanks
>|||Thanks for the help but unfortunately, I've already looked at that post
and the issue is a bit different.
The issue I'm having seems to have something to do with running a query
from a remote machine. So if I run a query on our SQL Server box from
my local desktop machine, I get the error. Running the query directly
on the SQL Server box while RDCed into the machine works flawlessly.
I did try to run a .vbs script from my machine which was able to query
the active directory...thanks for the link =). This leads me to
believe that it has something to do with SQL server security
restricting queries run from remote machines. I ran the surface area
configuration utility and didn't really see anything that jumped out at
me...
Anyone have any ideas?|||Alright, I've figured out a fix..
The AD linked server that I originally created was set to login to AD
with the credentials of the current security context. I changed this
to log in with a specified login and it worked fine. Whats strange is
that I set it to my own login account which I was using to run the
query remotely anyways. I guess SQL server queries ran remotely are
not run under the logged in users' security context after all?
Thanks for your help John =).
Can't run LDAP Query From Remote Machine
Hi all,
I have a SQL 2005 server with a linked server which points to our
active directory. I am able to query the active directory from the
local machine when RDC'ed into the server, but when I run the query
from a remote machine using Management Studio, I get this error:
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT *
FROM 'LDAP://prudc/DC=<domain>,DC=com'
" against OLE DB provider "ADSDSOObject" for linked server "ADSI".
The query is:
SELECT *
FROM OPENQUERY( ADSI,
'SELECT *
FROM ''LDAP://prudc/DC=<domain>,DC=com''
'
)
(note I replaced our domain name with <domain> in the above query and
error message)
This issue isn't specific to the above query as I've tried many ldap
queries and they have all worked on the local machine but failed on the
remote machine.
I'm completely stomped on this and would greatly appreciate any help I
can get.
ThanksHi Jim
This was a previous post when someone had the same error
http://tinyurl.com/pjg7s I am not sure how much use it will be!
If the query works on the server then I would expect it to be ok, which
probably leaves permission/access as the main issue. Can you use VB script t
o
query the AD e.g. using the scripts from http://www.rlmueller.net/?
John
"Jim" wrote:
> Hi all,
> I have a SQL 2005 server with a linked server which points to our
> active directory. I am able to query the active directory from the
> local machine when RDC'ed into the server, but when I run the query
> from a remote machine using Management Studio, I get this error:
>
> Msg 7320, Level 16, State 2, Line 1
> Cannot execute the query "SELECT *
> FROM 'LDAP://prudc/DC=<domain>,DC=com'
> " against OLE DB provider "ADSDSOObject" for linked server "ADSI".
>
> The query is:
> SELECT *
> FROM OPENQUERY( ADSI,
> 'SELECT *
> FROM ''LDAP://prudc/DC=<domain>,DC=com''
> '
> )
> (note I replaced our domain name with <domain> in the above query and
> error message)
>
> This issue isn't specific to the above query as I've tried many ldap
> queries and they have all worked on the local machine but failed on the
> remote machine.
> I'm completely stomped on this and would greatly appreciate any help I
> can get.
> Thanks
>|||Thanks for the help but unfortunately, I've already looked at that post
and the issue is a bit different.
The issue I'm having seems to have something to do with running a query
from a remote machine. So if I run a query on our SQL Server box from
my local desktop machine, I get the error. Running the query directly
on the SQL Server box while RDCed into the machine works flawlessly.
I did try to run a .vbs script from my machine which was able to query
the active directory...thanks for the link =). This leads me to
believe that it has something to do with SQL server security
restricting queries run from remote machines. I ran the surface area
configuration utility and didn't really see anything that jumped out at
me...
Anyone have any ideas?|||Alright, I've figured out a fix..
The AD linked server that I originally created was set to login to AD
with the credentials of the current security context. I changed this
to log in with a specified login and it worked fine. Whats strange is
that I set it to my own login account which I was using to run the
query remotely anyways. I guess SQL server queries ran remotely are
not run under the logged in users' security context after all?
Thanks for your help John =).
I have a SQL 2005 server with a linked server which points to our
active directory. I am able to query the active directory from the
local machine when RDC'ed into the server, but when I run the query
from a remote machine using Management Studio, I get this error:
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT *
FROM 'LDAP://prudc/DC=<domain>,DC=com'
" against OLE DB provider "ADSDSOObject" for linked server "ADSI".
The query is:
SELECT *
FROM OPENQUERY( ADSI,
'SELECT *
FROM ''LDAP://prudc/DC=<domain>,DC=com''
'
)
(note I replaced our domain name with <domain> in the above query and
error message)
This issue isn't specific to the above query as I've tried many ldap
queries and they have all worked on the local machine but failed on the
remote machine.
I'm completely stomped on this and would greatly appreciate any help I
can get.
ThanksHi Jim
This was a previous post when someone had the same error
http://tinyurl.com/pjg7s I am not sure how much use it will be!
If the query works on the server then I would expect it to be ok, which
probably leaves permission/access as the main issue. Can you use VB script t
o
query the AD e.g. using the scripts from http://www.rlmueller.net/?
John
"Jim" wrote:
> Hi all,
> I have a SQL 2005 server with a linked server which points to our
> active directory. I am able to query the active directory from the
> local machine when RDC'ed into the server, but when I run the query
> from a remote machine using Management Studio, I get this error:
>
> Msg 7320, Level 16, State 2, Line 1
> Cannot execute the query "SELECT *
> FROM 'LDAP://prudc/DC=<domain>,DC=com'
> " against OLE DB provider "ADSDSOObject" for linked server "ADSI".
>
> The query is:
> SELECT *
> FROM OPENQUERY( ADSI,
> 'SELECT *
> FROM ''LDAP://prudc/DC=<domain>,DC=com''
> '
> )
> (note I replaced our domain name with <domain> in the above query and
> error message)
>
> This issue isn't specific to the above query as I've tried many ldap
> queries and they have all worked on the local machine but failed on the
> remote machine.
> I'm completely stomped on this and would greatly appreciate any help I
> can get.
> Thanks
>|||Thanks for the help but unfortunately, I've already looked at that post
and the issue is a bit different.
The issue I'm having seems to have something to do with running a query
from a remote machine. So if I run a query on our SQL Server box from
my local desktop machine, I get the error. Running the query directly
on the SQL Server box while RDCed into the machine works flawlessly.
I did try to run a .vbs script from my machine which was able to query
the active directory...thanks for the link =). This leads me to
believe that it has something to do with SQL server security
restricting queries run from remote machines. I ran the surface area
configuration utility and didn't really see anything that jumped out at
me...
Anyone have any ideas?|||Alright, I've figured out a fix..
The AD linked server that I originally created was set to login to AD
with the credentials of the current security context. I changed this
to log in with a specified login and it worked fine. Whats strange is
that I set it to my own login account which I was using to run the
query remotely anyways. I guess SQL server queries ran remotely are
not run under the logged in users' security context after all?
Thanks for your help John =).
Can't run LDAP Query From Remote Machine
Hi all,
I have a SQL 2005 server with a linked server which points to our
active directory. I am able to query the active directory from the
local machine when RDC'ed into the server, but when I run the query
from a remote machine using Management Studio, I get this error:
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT *
FROM 'LDAP://prudc/DC=<domain>,DC=com'
" against OLE DB provider "ADSDSOObject" for linked server "ADSI".
The query is:
SELECT *
FROM OPENQUERY( ADSI,
'SELECT *
FROM ''LDAP://prudc/DC=<domain>,DC=com''
'
)
(note I replaced our domain name with <domain> in the above query and
error message)
This issue isn't specific to the above query as I've tried many ldap
queries and they have all worked on the local machine but failed on the
remote machine.
I'm completely stomped on this and would greatly appreciate any help I
can get.
Thanks
Hi Jim
This was a previous post when someone had the same error
http://tinyurl.com/pjg7s I am not sure how much use it will be!
If the query works on the server then I would expect it to be ok, which
probably leaves permission/access as the main issue. Can you use VB script to
query the AD e.g. using the scripts from http://www.rlmueller.net/?
John
"Jim" wrote:
> Hi all,
> I have a SQL 2005 server with a linked server which points to our
> active directory. I am able to query the active directory from the
> local machine when RDC'ed into the server, but when I run the query
> from a remote machine using Management Studio, I get this error:
>
> Msg 7320, Level 16, State 2, Line 1
> Cannot execute the query "SELECT *
> FROM 'LDAP://prudc/DC=<domain>,DC=com'
> " against OLE DB provider "ADSDSOObject" for linked server "ADSI".
>
> The query is:
> SELECT *
> FROM OPENQUERY( ADSI,
> 'SELECT *
> FROM ''LDAP://prudc/DC=<domain>,DC=com''
> '
> )
> (note I replaced our domain name with <domain> in the above query and
> error message)
>
> This issue isn't specific to the above query as I've tried many ldap
> queries and they have all worked on the local machine but failed on the
> remote machine.
> I'm completely stomped on this and would greatly appreciate any help I
> can get.
> Thanks
>
|||Thanks for the help but unfortunately, I've already looked at that post
and the issue is a bit different.
The issue I'm having seems to have something to do with running a query
from a remote machine. So if I run a query on our SQL Server box from
my local desktop machine, I get the error. Running the query directly
on the SQL Server box while RDCed into the machine works flawlessly.
I did try to run a .vbs script from my machine which was able to query
the active directory...thanks for the link =). This leads me to
believe that it has something to do with SQL server security
restricting queries run from remote machines. I ran the surface area
configuration utility and didn't really see anything that jumped out at
me...
Anyone have any ideas?
|||Alright, I've figured out a fix..
The AD linked server that I originally created was set to login to AD
with the credentials of the current security context. I changed this
to log in with a specified login and it worked fine. Whats strange is
that I set it to my own login account which I was using to run the
query remotely anyways. I guess SQL server queries ran remotely are
not run under the logged in users' security context after all?
Thanks for your help John =).
I have a SQL 2005 server with a linked server which points to our
active directory. I am able to query the active directory from the
local machine when RDC'ed into the server, but when I run the query
from a remote machine using Management Studio, I get this error:
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT *
FROM 'LDAP://prudc/DC=<domain>,DC=com'
" against OLE DB provider "ADSDSOObject" for linked server "ADSI".
The query is:
SELECT *
FROM OPENQUERY( ADSI,
'SELECT *
FROM ''LDAP://prudc/DC=<domain>,DC=com''
'
)
(note I replaced our domain name with <domain> in the above query and
error message)
This issue isn't specific to the above query as I've tried many ldap
queries and they have all worked on the local machine but failed on the
remote machine.
I'm completely stomped on this and would greatly appreciate any help I
can get.
Thanks
Hi Jim
This was a previous post when someone had the same error
http://tinyurl.com/pjg7s I am not sure how much use it will be!
If the query works on the server then I would expect it to be ok, which
probably leaves permission/access as the main issue. Can you use VB script to
query the AD e.g. using the scripts from http://www.rlmueller.net/?
John
"Jim" wrote:
> Hi all,
> I have a SQL 2005 server with a linked server which points to our
> active directory. I am able to query the active directory from the
> local machine when RDC'ed into the server, but when I run the query
> from a remote machine using Management Studio, I get this error:
>
> Msg 7320, Level 16, State 2, Line 1
> Cannot execute the query "SELECT *
> FROM 'LDAP://prudc/DC=<domain>,DC=com'
> " against OLE DB provider "ADSDSOObject" for linked server "ADSI".
>
> The query is:
> SELECT *
> FROM OPENQUERY( ADSI,
> 'SELECT *
> FROM ''LDAP://prudc/DC=<domain>,DC=com''
> '
> )
> (note I replaced our domain name with <domain> in the above query and
> error message)
>
> This issue isn't specific to the above query as I've tried many ldap
> queries and they have all worked on the local machine but failed on the
> remote machine.
> I'm completely stomped on this and would greatly appreciate any help I
> can get.
> Thanks
>
|||Thanks for the help but unfortunately, I've already looked at that post
and the issue is a bit different.
The issue I'm having seems to have something to do with running a query
from a remote machine. So if I run a query on our SQL Server box from
my local desktop machine, I get the error. Running the query directly
on the SQL Server box while RDCed into the machine works flawlessly.
I did try to run a .vbs script from my machine which was able to query
the active directory...thanks for the link =). This leads me to
believe that it has something to do with SQL server security
restricting queries run from remote machines. I ran the surface area
configuration utility and didn't really see anything that jumped out at
me...
Anyone have any ideas?
|||Alright, I've figured out a fix..
The AD linked server that I originally created was set to login to AD
with the credentials of the current security context. I changed this
to log in with a specified login and it worked fine. Whats strange is
that I set it to my own login account which I was using to run the
query remotely anyways. I guess SQL server queries ran remotely are
not run under the logged in users' security context after all?
Thanks for your help John =).
Subscribe to:
Posts (Atom)