Database connection String Using 'win32ole'

[Ruby Mania]

My Connection String:
def open(database)
# Open ADO connection to the SQL Server database
connection_string = "Provider=SQLOLEDB.1;"
connection_string << "Persist Security Info=False;"
connection_string << "User ID=#{@username};"
connection_string << "password=#{@password};"
connection_string << "Initial Catalog=#{database};"
connection_string << "Data Source=#{@host};"
connection_string << "Network Library=dbmssocn"
@connection = WIN32OLE.new('ADODB.Connection')
@connection.Open(connection_string)
end

The string gives error when I using the domain credentials. EG:

Username: domain\<username>
Password: whatever

Error:
OLE error code:80040E4D in Microsoft OLE DB Provider for SQL Server
Login failed for user 'domain\<username>'.

Please help !
Thanks ton !

 

[Alex Stahl]

[Note: parts of this message were removed to make it a legal post.]

What type of authentication does your SQL Server use? Windows or SQL
Server? If it's configured to use SQL Server auth, then it has no idea
who the user 'domain\<username>' is, since that's an AD user, not a SQL
user.

 

[Ruby Mania]

Thanks a lot for the quick reponse. I have mixed mode authentication
enabled.
When I am using SQL Server auth it works like a charm.

I get error when I am using Windows authentication: 'domain\<username>'

Thanks

====

Alex Stahl wrote in post #956435:

 

[Ruby Mania]

Thanks , IT Works. I modified it to:

connection_string = "Provider=SQLOLEDB.1;"
connection_string << "Persist Security Info=False;"
connection_string << "Initial Catalog=#{database};"
connection_string << "Data Source=#{@host};"
connection_string << "Trusted_Connection=Yes;"
connection = WIN32OLE.new('ADODB.Connection')

 

[Charles Calvert]

Thanks , IT Works. I modified it to:

connection_string = "Provider=SQLOLEDB.1;"
connection_string << "Persist Security Info=False;"
connection_string << "Initial Catalog=#{database};"
connection_string << "Data Source=#{@host};"
connection_string << "Trusted_Connection=Yes;"
connection = WIN32OLE.new('ADODB.Connection')

To clarify, using the trusted connection attribute in the connection
string is equivalent to using "Integrated Security=SSPI". It causes
the connection to be authenticated using the Windows credentials of
the account that started the program making the connection.

Your original attempt to embed the username and password in the string
caused SQL Server to attempt to authenticate the connection using the
SQL Server account with that username.

This article might be useful
<http://msdn.microsoft.com/en-us/library/ff647396.aspx>. It's aimed
as ASP.NET and SQL Server 2000, but much of the information should
still be applicable.