Is it possible to use a trusted connection (SSPI) with the SQLDMO API?

Question

I am using the DMO API via .NET to provide an alternative interface to job scheduling functionality on SQL Server 2000 Agent. The working code looks something like this:

using SQLDMO;

internal class TestDmo {
    public void StartJob() {
        SQLServerClass sqlServer = new SQLServerClass();
        sqlServer.Connect("MyServerName", "sql_user_id", "p@ssword"); // no trusted/SSPI overload?

        foreach (Job job in sqlServer.JobServer.Jobs) {
            if (!job.Name.Equals("MyJob")) continue;

            job.Start(null);
        }
    }
}

Everything works in the above-listed form (SQL Server authentication with uid/pwd provided) but I would also like to provide an option to authenticate as a trusted user (aka SSPI, Trusted Connection)

Is this possible in the DMO API? If so how?

Note: The SQLServerClass.Connect method does not seem to have any overloads, I already tried to pass null values for the user id and password to no avail and the Googles has not been helpful yet. Any ideas?

score 4 · Accepted Answer · answered Apr 01 '11 at 17:17

From the documentation:

object.Connect( [ ServerName ] , [ Login ] , [ Password ] )

[...]

Use the Login and Password arguments to specify values used for SQL Server Authentication. To use Windows Authentication for the connection, set the LoginSecure property to TRUE prior to calling the Connect method. When LoginSecure is TRUE, any values provided in the Login and Password arguments are ignored.

Thus, you have to set the LoginSecure property to true before calling Connect. Then, it does not matter which values you pass for the last two parameters.

Thanks for the answer. Now I need to add another term to the SSPI/Trusted Connection/Integrated Security family of terms: LoginSecure — Paul Sasik, Apr 01 '11 at 20:04

score 2 · Answer 2 · answered Apr 01 '11 at 17:14

2

Sure, you can use the LoginSecure property:

SQLServerClass sqlServer = new SQLServerClass();
sqlServer.LoginSecure = true;
sqlServer.Connect("MyServerName", null, null);

(actually, I don't remember if you must pas null or empty strings...)

answered Apr 01 '11 at 17:14

Simon Mourier

132,049
21
248
298

Never mind whether you leave them null or pass empty strings. Once you set LoginSecure = true, user name and password values are ignored. – CesarGon Apr 01 '11 at 17:20
+1 Thanks for the great answer. The check mark goes Heinzi though based on timestamp. That answer landed 2 or 3 minutes earlier. – Paul Sasik Apr 01 '11 at 20:02
1

@Paul Sasik - Actually, I think you're wrong, my answer was 17:14:14 and Heinzi's 17:17:46, but no hard feelings. – Simon Mourier Apr 02 '11 at 07:38

score 2 · Answer 3 · answered Apr 01 '11 at 17:14

2

Set SQLServerClass.LoginSecure = true and leave user name and password null.

Have a look here for more information. I just noticed that LoginSecure is deprecated, though. Apparently, SQL-DMO has been superseded by SMO.

answered Apr 01 '11 at 17:14

CesarGon

15,099
6
57
85

SMO is the newer, better, slicker API but its backward compatibility to MS SQL 2k is somewhat problematic. I need to use DMO so as not to burden the system with different compatibility settings etc. – Paul Sasik Apr 01 '11 at 17:28
@Paul Sasik: Fair enough. :-) – CesarGon Apr 01 '11 at 17:29
+1 Thanks for the great answer. The check mark goes Heinzi though based on timestamp. That answer landed 2 or 3 minutes earlier. – Paul Sasik Apr 01 '11 at 20:02
@Paul Sasik: Well, if you're looking at timestamps, this is what it is: Heinzi: 1-April 17:17:46; CesarGon: 1-April 17:14:55; Simon Mourier: 17:14:14. Simon was first, then I, and Heinzi was last. :-) – CesarGon Apr 02 '11 at 11:17

Is it possible to use a trusted connection (SSPI) with the SQLDMO API?

3 Answers3