How can I add a Domain into the UserName of a Provider String using Win32::OLE

[Incognito]

When using a provider string with a local SQL Server user, say "MyName" with a password "MyPassword", a typical string would look like this:

use Win32::OLE;
use Win32::OLE::Const 'Microsoft ActiveX Data Objects';

$strProvider = 'Provider=sqloledb;Data Source=MyServerName;Initial Cat
+alog=MyCatalog;';
$strProvider .= 'User Id=MyName;Password=MyPassword';
    
$strQuery = "SELECT 1 AS SystemName";
    
$objConn = CreateObject Win32::OLE "ADODB.Connection";
$objConn->Open($strProvider);

$objRS = $objConn->Execute($strQuery);
if ($objRS) {
    while ( !$objRS->EOF ) {
        my($SystemName) = ( $objRS->Fields('SystemName')->value );
        print "SystemName: ", $SystemName, "\n";
        $objRS->MoveNext;
    }
} else {
        print "This doesn't work!!!\n";
}

This code should print a simple "1" as data... and this does indeed work. Now, if we go and add a Domain Name to the User, things don't work, and I can't figure out why... I've tried many different variations with the "/" (I've done double backslash, double forward slash, singles, even quads, but none of them work).

This is what the provider string would look like:

$strProvider = 'Provider=sqloledb;Data Source=MyServerName;Initial Cat
+alog=MyCatalog;';
$strProvider .= 'User Id=MYMACHINE\MyName;Password=MyPassword';

This doesn't work, and it's really bugging me... Anyone have any ideas as to how to get a user that has a domain name to connect to a SQL Server?

[NetWallah]

You need to figure out, or specify whether you are using TCP/IP or named-pipes to connect to the SQL server. If named-pipes, and it is Local, you should not need any authentication.

You also need to figure out whether you are using SQL or NT authentication. Only NT authentication requires domain name.

Check this site for assistance with connection strings.

[Incognito]

I appreciate your reply. The account is an NT Account, so it requires NT authentication. I can get this to work with a local SQL account (basically the example above)...

The link you provided, while incredibly useful, was the one I originally got help from (and unfortunately mentions nothing about domains in a username).

As for the TCP/IP vs. Names-pipes, I'm not 100% sure what's going on, but I'm pretty sure we need authentication, because this is happening in a company intranet, and the SQL calls being made in the Perl app must be made as a specific NT domain user.

Thanks again for your response, and I hope someone out there has a suggestion on how to connect to the SQL database using an NT domain user.

[NetWallah]

Try adding this to your connection string:

"Network Library=DBMSSOCN;"

- "Network Library=DBMSSOCN" tells OLE DB to use TCP/IP rather than Named Pipes (Q238949).

Include the parameters "UID" and "PWD" when you use ODBC, and "User ID" and "Password" when you use the SQLOLEDB provider.

[jdporter]

This is PerlMonks, not ADOMonks. The answer to your question is in no way related to the fact that you're coding in perl.