Database connection String Using 'win32ole'

Ruby
1

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 !

2

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.

3

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 S. wrote in post #956435:

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.

4

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’)

5

On Fri, 22 Oct 2010 17:16:08 -0500, Ruby M. [email protected]
wrote in [email protected]:

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.