August 16, 2006 at 12:39 pm
my SQL Server 2000 install is on a windows 2003 server which was a member of an NT domain. i just switched it over to our Active Directory and now some of my stored procedures aren't working.
the stored procedure in question looks like this:
CREATE PROCEDURE dbo.erehabDTS_import AS
EXEC master..xp_cmdshell 'dtsrun /S localhost /U ColdFusionUser /P ColdFusionPassword /N erehab_import'
GO
so basically it runs a DTS package. the reason i needed a stored procedure to call a DTS package was so that i could call the DTS package from a ColdFusion Script.
the error i am getting when i try to run the cf script is as follows:
A Database error was encountered
Error Message: Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]xpsql.cpp: Error 1788 from LogonUserW on line 620
i searched google for the SQL error code 1788 and it says that 1788 means ERROR_TRUSTED_DOMAIN_FAILURE
The trust relationship between the primary domain and the trusted domain failed.
the username specified in the stored procedure - ColdFusionUser can be found in enterprise manager under server -> Security -> Logins
in the properties window for this user in the general tab "SQL Server Authentication" is selected (although both options are grayed out so it cannot be changed). default database and language are correct.
so my question is, if this is a local SQL Server user using SQL Server Authentication, what does it have to do with the trust between AD and the old domain?
any help would be appreciated.
August 17, 2006 at 6:52 am
anyone??? i am really stuck here.
August 17, 2006 at 6:59 am
Does ColdFusionUser have execute rights on xp_cmdshell?
August 17, 2006 at 9:23 am
I assume so because it is the same user that was running the sp before the migration without any problems but how would i check this?
August 17, 2006 at 9:52 am
Make sure the user ColdFusionUser has the correct permissions on the folder containing the file you are trying to import. Server 2003 locks down permissions, so you have to specifically grant read access to folders. If the file is somewhere out there in the network, you'll want to use an account that is in AD, not a local system account.
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
August 17, 2006 at 10:02 am
ColdFusionUser is not a networkuser, it is a SQL user using SQL server authentication. so how would i go about giving a SQL user access to a network folder?
August 17, 2006 at 10:23 am
coldFusionUser is a SQL login with just SQL Server Authentication - so there is not domain account for him. i don't think i could assign permissions to any folders with that user.
so just for the heck of it, i entered a nonexistent username into the stored procedure and i still get the 1788 database error.
so that would lead me to believe that it is not a permissions thing. what the heck else could it be?
August 17, 2006 at 11:27 am
i just looked in the event viewer on the SQL server and every tiem i run my CF template i see an entry like the following in the security log. so somthign is tryign to log in with the odl NT domains admin account but i cant figure out what it is.
Event Type: Failure Audit
Event Source: Security
Event Category: Logon/Logoff
Event ID: 537
Date: 8/17/2006
Time: 1:10:38 PM
User: NT AUTHORITY\SYSTEM
Computer: SQL1
Description:
Logon Failure:
Reason: An error occurred during logon
User Name: Administrator
Domain: my old NT domain
Logon Type: 4
Logon Process: Advapi
Authentication Package: Negotiate
Workstation Name: SQL1
Status code: 0xC000018C
Substatus code: 0x0
Caller User Name: Administrator
Caller Domain: SVAD
Caller Logon ID: (0x0,0xDC7B)
Caller Process ID: 448
Transited Services: -
Source Network Address: -
Source Port: -
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
August 17, 2006 at 12:50 pm
A couple of things come to mind.
Did you remove Builtin\Administrators from the sysadmin role on the server ?
or
Did you deny login to Builtin\Administrators on the server ?
This might explain the behavior. In any event here's a couple of commands you may wany to try:
exec sp_grantlogin 'NT AUTHORITY\SYSTEM'
exec sp_addsrvrolemember @loginame='NT AUTHORITY\SYSTEM', @rolename='sysadmin'
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 17, 2006 at 2:01 pm
thanks for your reply but o i didnt do either of those things - and i tryed running both commands you suggested and no effect.
August 17, 2006 at 2:15 pm
Have you registered the SPN for the SQL Server service account?
A.J.
DBA with an attitude
August 17, 2006 at 2:19 pm
sorry i dont know what that is. what is the SPN?
August 18, 2006 at 6:41 am
August 18, 2006 at 6:58 am
yea that works.
August 18, 2006 at 7:29 am
And you can do a simple select from one of your tables from a CF webpage like this...
<cfquery name="myqry" datasource="whatever" etc...>
Select Col1 from Table1
</cfquery>
<cfoutput query="myqry">
#col1#<br>
</cfoutput>
Or will it give you your permissions error then as well?
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply