Although I am a somewhat experienced Oracle DBO I have been working on a SQL Server 7 problem.
I user the DTS Wizard to make a DTS package and the package works fine when I execute it manually, whether it is to create a local text file or to upload to a remote server.
at any point when I try to schedule the the package I get this error:
Microsoft SQL-DMO (ODBC SQLState: 42000)
[Micorsoft][ODBC SQL Server Driver][SQL Server][Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server Connection
I get this message as soon as I click on 'schedule task', whether its to creat a text file or connect to a remote server. Whether I user NT Authentication or SQL server authentication. I cant seen to find this error on any of the search engines either.
Maybe I dont know because I am new with SQL Server, but does anyone have any ideas?
The scheduler is run under the 'SQL Server Agent', if you go to properties screen you will see a header 'Service startup account'. If it is set to 'System account' you should set it to 'This account' and choose a domain acount that has the proper rights.
At the site that I am at I've created a new NT user called SQLAdmin which is part of the NT Domain Administartor group and I have the service startup as this user.
When you use the SQL Server ODBC driver version 2000.080.0194, which was released with SQL Server 2000 and Microsoft Data Access Components (MDAC) 2.6 RTM, the authentication settings for ODBC data source names (DSNs) are ignored. The driver attempts to log directly into SQL Server using NTLM authentication (integrated security) without prompting for a user ID and password.
This can cause authentication failures, messages that database objects are not available, or unintentional modification of the wrong objects when copies of objects are maintained in multiple databases.
To resolve this problem, obtain the latest service pack for Microsoft Data Access Components 2.6. For additional information, please see the following article in the Microsoft Knowledge Base: