# ActiveRecord connect to SqlServer

**URL:** <https://rubytalk.org/t/activerecord-connect-to-sqlserver/44018>\
**Category:** ruby-talk\
**Created:** [24 January 2008 15:59 UTC](https://rubytalk.org/t/activerecord-connect-to-sqlserver/44018 "2008-01-24T15:59:57Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![dkmd\_nielsen1](https://avatars.discourse-cdn.com/v4/letter/d/977dab/32.png) [@dkmd\_nielsen1](https://rubytalk.org/u/dkmd_nielsen1)\
**Post date:** [24 January 2008 15:59 UTC](https://rubytalk.org/t/activerecord-connect-to-sqlserver/44018/1 "2008-01-24T15:59:57Z")

</div>

I know you guys have beaten this subject to death, but I'm just not  
getting a connection to SqlServer 2005. It is installed locally, with  
an instance name of DVN-P548. I use my windows user name and password  
to log in.

require 'rubygems'  
require\_gem 'activerecord'

ActiveRecord::Base.establish\_connection(  
&nbsp;&nbsp;:adapter =\> 'sqlserver',  
&nbsp;&nbsp;:mode =\> 'ADO',  
&nbsp;&nbsp;:host =\> 'P548-DVN',  
&nbsp;&nbsp;:dsn =\> '???', \*\* what is this supposed  
to be???  
&nbsp;&nbsp;:database =\> 'CountsAAN',  
&nbsp;&nbsp;:username =\> 'my\_windows\_userid',  
&nbsp;&nbsp;:password =\> 'my\_password'  
)

class WiRecord \< ActiveRecord::Base  
end

Errors I get returned are either "Server does not exist or access is  
denied" (thanks, MS, for narrowing this one down a bit), or "user is  
not trusted", depending on what values I have attempted for host and  
dsn. What is host and dsn? Believe it or not, I do not find a clear  
definition in the rails handbook.

Do I want to use ADO, or should I use ODBC? If odbc, where does one  
find the connection string? Being a windows user, I'm so used to  
having everything done for me under the covers that I don't understand  
any of the underlying details.

Thanks for everything,  
dvn

---

<div class="post-metadata">

**Author:** ![dkmd\_nielsen1](https://avatars.discourse-cdn.com/v4/letter/d/977dab/32.png) [@dkmd\_nielsen1](https://rubytalk.org/u/dkmd_nielsen1)\
**Post date:** [24 January 2008 17:10 UTC](https://rubytalk.org/t/activerecord-connect-to-sqlserver/44018/2 "2008-01-24T17:10:08Z")

</div>

ActiveRecord::Base.establish\_connection(  
&nbsp;&nbsp;:adapter =\> 'sqlserver',  
&nbsp;&nbsp;:mode =\> 'ADO',  
&nbsp;&nbsp;:host =\> 'DVN-P548',  
&nbsp;&nbsp;:dsn =\> 'LocalSqlServer2005',  
&nbsp;&nbsp;:database =\> 'CountsAAN',  
&nbsp;&nbsp;:username =\> 'my\_username',  
&nbsp;&nbsp;:password =\> 'my\_password'  
)

The above configuration yields a Login failed for user. I've  
confirmed through Windows Services that my user name and password are  
valid for logging into my server instance. I've tried user name in  
the formats of "my\_username" and "domain\my\_username", and both fail  
with the same error: "Login failed for user 'my\_username'. HRESULT  
error code:0x80020009". So I'm at a loss. I don't know what else to  
check.

dkmd\_nielsen wrote:

> **···**
>
> > I know you guys have beaten this subject to death, but I'm just not  
> > getting a connection to SqlServer 2005. It is installed locally, with  
> > an instance name of DVN-P548. I use my windows user name and password  
> > to log in.
> > 
> > require 'rubygems'  
> > require\_gem 'activerecord'
> > 
> > ActiveRecord::Base.establish\_connection(  
> > &nbsp;&nbsp;:adapter =\> 'sqlserver',  
> > &nbsp;&nbsp;:mode =\> 'ADO',  
> > &nbsp;&nbsp;:host =\> 'P548-DVN',  
> > &nbsp;&nbsp;:dsn =\> '???', \*\* what is this supposed  
> > to be???  
> > &nbsp;&nbsp;:database =\> 'CountsAAN',  
> > &nbsp;&nbsp;:username =\> 'my\_windows\_userid',  
> > &nbsp;&nbsp;:password =\> 'my\_password'  
> > )
> > 
> > class WiRecord \< ActiveRecord::Base  
> > end
> > 
> > Errors I get returned are either "Server does not exist or access is  
> > denied" (thanks, MS, for narrowing this one down a bit), or "user is  
> > not trusted", depending on what values I have attempted for host and  
> > dsn. What is host and dsn? Believe it or not, I do not find a clear  
> > definition in the rails handbook.
> > 
> > Do I want to use ADO, or should I use ODBC? If odbc, where does one  
> > find the connection string? Being a windows user, I'm so used to  
> > having everything done for me under the covers that I don't understand  
> > any of the underlying details.
> > 
> > Thanks for everything,  
> > dvn

---

<div class="post-metadata">

**Author:** ![Todd\_Benson](https://avatars.discourse-cdn.com/v4/letter/t/34f0e0/32.png) [@Todd\_Benson](https://rubytalk.org/u/Todd_Benson)\
**Post date:** [24 January 2008 17:34 UTC](https://rubytalk.org/t/activerecord-connect-to-sqlserver/44018/3 "2008-01-24T17:34:33Z")

</div>

> I know you guys have beaten this subject to death, but I'm just not  
> getting a connection to SqlServer 2005. It is installed locally, with  
> an instance name of DVN-P548. I use my windows user name and password  
> to log in.
> 
> require 'rubygems'  
> require\_gem 'activerecord'
> 
> ActiveRecord::Base.establish\_connection(  
> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:adapter =\> 'sqlserver',  
> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:mode =\> 'ADO',  
> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:host =\> 'P548-DVN',

Why is this not DVN-P548?

> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:dsn =\> '???', \*\* what is this supposed  
> to be???

You shouldn't need that part of the Hash. Try leaving the :dsn part out.

> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:database =\> 'CountsAAN',  
> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:username =\> 'my\_windows\_userid',  
> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:password =\> 'my\_password'  
> )
> 
> class WiRecord \< ActiveRecord::Base  
> end
> 
> Errors I get returned are either "Server does not exist or access is  
> denied" (thanks, MS, for narrowing this one down a bit), or "user is  
> not trusted"

That last error is a bit weird. That means you're connecting to  
something, but failing. Maybe look at your permissions?

> , depending on what values I have attempted for host and  
> dsn. What is host and dsn? Believe it or not, I do not find a clear  
> definition in the rails handbook.

"host" is the name of the machine you are running the database on.  
This can also be an IP address. If the database is on the same  
machine, use 127.0.0.1 or localhost.

> Do I want to use ADO, or should I use ODBC? If odbc, where does one  
> find the connection string? Being a windows user, I'm so used to  
> having everything done for me under the covers that I don't understand  
> any of the underlying details.

Hopefully somebody else can answer this.

> Thanks for everything,  
> dvn

Todd

> **···**
>
> On Jan 24, 2008 9:59 AM, dkmd\_nielsen \<donn@cmscms.com\> wrote:

---

<div class="post-metadata">

**Author:** ![Dejan\_Dimic](https://avatars.discourse-cdn.com/v4/letter/d/e19adc/32.png) [@Dejan\_Dimic](https://rubytalk.org/u/Dejan_Dimic)\
**Post date:** [25 January 2008 10:04 UTC](https://rubytalk.org/t/activerecord-connect-to-sqlserver/44018/4 "2008-01-25T10:04:57Z")

</div>

You can make a User DSN throughout ODBC Data Source Administrator for  
example named MyExpressDB using SQL Native Client driver.

Then your connection settings should be:

ActiveRecord::Base.establish\_connection(  
&nbsp;&nbsp;:adapter =\> "sqlserver",  
&nbsp;&nbsp;:mode =\> "ODBC",  
&nbsp;&nbsp;:dsn =\> "MyExpressDB"  
)

and sit back and enjoy

> **···**
>
> On Jan 24, 6:34 pm, Todd Benson \<caduce...@gmail.com\> wrote:
> 
> > On Jan 24, 2008 9:59 AM, dkmd\_nielsen \<d...@cmscms.com\> wrote:
> > 
> > \> I know you guys have beaten this subject to death, but I'm just not  
> > \> getting a connection to SqlServer 2005. It is installed locally, with  
> > \> an instance name of DVN-P548. I use my windows user name and password  
> > \> to log in.
> > 
> > \> require 'rubygems'  
> > \> require\_gem 'activerecord'
> > 
> > \> ActiveRecord::Base.establish\_connection(  
> > \> :adapter =\> 'sqlserver',  
> > \> :mode =\> 'ADO',  
> > \> :host =\> 'P548-DVN',
> > 
> > Why is this not DVN-P548?
> > 
> > \> :dsn =\> '???', \*\* what is this supposed  
> > \> to be???
> > 
> > You shouldn't need that part of the Hash. Try leaving the :dsn part out.
> > 
> > \> :database =\> 'CountsAAN',  
> > \> :username =\> 'my\_windows\_userid',  
> > \> :password =\> 'my\_password'  
> > \> )
> > 
> > \> class WiRecord \< ActiveRecord::Base  
> > \> end
> > 
> > \> Errors I get returned are either "Server does not exist or access is  
> > \> denied" (thanks, MS, for narrowing this one down a bit), or "user is  
> > \> not trusted"
> > 
> > That last error is a bit weird. That means you're connecting to  
> > something, but failing. Maybe look at your permissions?
> > 
> > \> , depending on what values I have attempted for host and  
> > \> dsn. What is host and dsn? Believe it or not, I do not find a clear  
> > \> definition in the rails handbook.
> > 
> > "host" is the name of the machine you are running the database on.  
> > This can also be an IP address. If the database is on the same  
> > machine, use 127.0.0.1 or localhost.
> > 
> > \> Do I want to use ADO, or should I use ODBC? If odbc, where does one  
> > \> find the connection string? Being a windows user, I'm so used to  
> > \> having everything done for me under the covers that I don't understand  
> > \> any of the underlying details.
> > 
> > Hopefully somebody else can answer this.
> > 
> > \> Thanks for everything,  
> > \> dvn
> > 
> > Todd

---

<div class="post-metadata">

**Author:** ![Paul\_Rogers1](https://avatars.discourse-cdn.com/v4/letter/p/b9bd4f/32.png) [@Paul\_Rogers1](https://rubytalk.org/u/Paul_Rogers1)\
**Post date:** [26 January 2008 02:34 UTC](https://rubytalk.org/t/activerecord-connect-to-sqlserver/44018/5 "2008-01-26T02:34:57Z")

</div>

I think this error "Server does not exist or access is  
denied" (thanks, MS, for narrowing this one down a bit), or "user is  
not trusted" means you are trying to access a sql server that is  
domain authenticated by supplying a sql server authenticated user

Also, when I used ado some time back with rails I got lots of errors,  
but switching to odbc mae it work fine.

Paul

> **···**
>
> On Jan 24, 10:34 am, Todd Benson \<caduce...@gmail.com\> wrote:
> 
> > On Jan 24, 2008 9:59 AM, dkmd\_nielsen \<d...@cmscms.com\> wrote:
> > 
> > \> I know you guys have beaten this subject to death, but I'm just not  
> > \> getting a connection to SqlServer 2005. It is installed locally, with  
> > \> an instance name of DVN-P548. I use my windows user name and password  
> > \> to log in.
> > 
> > \> require 'rubygems'  
> > \> require\_gem 'activerecord'
> > 
> > \> ActiveRecord::Base.establish\_connection(  
> > \> :adapter =\> 'sqlserver',  
> > \> :mode =\> 'ADO',  
> > \> :host =\> 'P548-DVN',
> > 
> > Why is this not DVN-P548?
> > 
> > \> :dsn =\> '???', \*\* what is this supposed  
> > \> to be???
> > 
> > You shouldn't need that part of the Hash. Try leaving the :dsn part out.
> > 
> > \> :database =\> 'CountsAAN',  
> > \> :username =\> 'my\_windows\_userid',  
> > \> :password =\> 'my\_password'  
> > \> )
> > 
> > \> class WiRecord \< ActiveRecord::Base  
> > \> end
> > 
> > \> Errors I get returned are either "Server does not exist or access is  
> > \> denied" (thanks, MS, for narrowing this one down a bit), or "user is  
> > \> not trusted"
> > 
> > That last error is a bit weird. That means you're connecting to  
> > something, but failing. Maybe look at your permissions?
> > 
> > \> , depending on what values I have attempted for host and  
> > \> dsn. What is host and dsn? Believe it or not, I do not find a clear  
> > \> definition in the rails handbook.
> > 
> > "host" is the name of the machine you are running the database on.  
> > This can also be an IP address. If the database is on the same  
> > machine, use 127.0.0.1 or localhost.
> > 
> > \> Do I want to use ADO, or should I use ODBC? If odbc, where does one  
> > \> find the connection string? Being a windows user, I'm so used to  
> > \> having everything done for me under the covers that I don't understand  
> > \> any of the underlying details.
> > 
> > Hopefully somebody else can answer this.
> > 
> > \> Thanks for everything,  
> > \> dvn
> > 
> > Todd

---

<div class="post-metadata">

**Author:** ![dkmd\_nielsen1](https://avatars.discourse-cdn.com/v4/letter/d/977dab/32.png) [@dkmd\_nielsen1](https://rubytalk.org/u/dkmd_nielsen1)\
**Post date:** [25 January 2008 21:55 UTC](https://rubytalk.org/t/activerecord-connect-to-sqlserver/44018/6 "2008-01-25T21:55:02Z")

</div>

Thanks to all. I setup the ODBC connection and it is working  
perfectly. All I did was change the :mode to "ODBC", and that was it.

dvn

> **···**
>
> On Jan 25, 4:00 am, Dejan Dimic \<dejan.di...@gmail.com\> wrote:
> 
> > On Jan 24, 6:34 pm, Todd Benson \<caduce...@gmail.com\> wrote:
> > 
> > \> On Jan 24, 2008 9:59 AM, dkmd\_nielsen \<d...@cmscms.com\> wrote:
> > 
> > \> \> I know you guys have beaten this subject to death, but I'm just not  
> > \> \> getting a connection to SqlServer 2005. It is installed locally, with  
> > \> \> an instance name of DVN-P548. I use my windows user name and password  
> > \> \> to log in.
> > 
> > \> \> require 'rubygems'  
> > \> \> require\_gem 'activerecord'
> > 
> > \> \> ActiveRecord::Base.establish\_connection(  
> > \> \> :adapter =\> 'sqlserver',  
> > \> \> :mode =\> 'ADO',  
> > \> \> :host =\> 'P548-DVN',
> > 
> > \> Why is this not DVN-P548?
> > 
> > \> \> :dsn =\> '???', \*\* what is this supposed  
> > \> \> to be???
> > 
> > \> You shouldn't need that part of the Hash. Try leaving the :dsn part out.
> > 
> > \> \> :database =\> 'CountsAAN',  
> > \> \> :username =\> 'my\_windows\_userid',  
> > \> \> :password =\> 'my\_password'  
> > \> \> )
> > 
> > \> \> class WiRecord \< ActiveRecord::Base  
> > \> \> end
> > 
> > \> \> Errors I get returned are either "Server does not exist or access is  
> > \> \> denied" (thanks, MS, for narrowing this one down a bit), or "user is  
> > \> \> not trusted"
> > 
> > \> That last error is a bit weird. That means you're connecting to  
> > \> something, but failing. Maybe look at your permissions?
> > 
> > \> \> , depending on what values I have attempted for host and  
> > \> \> dsn. What is host and dsn? Believe it or not, I do not find a clear  
> > \> \> definition in the rails handbook.
> > 
> > \> "host" is the name of the machine you are running the database on.  
> > \> This can also be an IP address. If the database is on the same  
> > \> machine, use 127.0.0.1 or localhost.
> > 
> > \> \> Do I want to use ADO, or should I use ODBC? If odbc, where does one  
> > \> \> find the connection string? Being a windows user, I'm so used to  
> > \> \> having everything done for me under the covers that I don't understand  
> > \> \> any of the underlying details.
> > 
> > \> Hopefully somebody else can answer this.
> > 
> > \> \> Thanks for everything,  
> > \> \> dvn
> > 
> > \> Todd
> > 
> > You can make a User DSN throughout ODBC Data Source Administrator for  
> > example named MyExpressDB using SQL Native Client driver.
> > 
> > Then your connection settings should be:
> > 
> > ActiveRecord::Base.establish\_connection(  
> > &nbsp;&nbsp;:adapter =\> "sqlserver",  
> > &nbsp;&nbsp;:mode =\> "ODBC",  
> > &nbsp;&nbsp;:dsn =\> "MyExpressDB"  
> > )
> > 
> > and sit back and enjoy

---

<div class="post-metadata">

**Author:** ![Softmind\_Technology](https://avatars.discourse-cdn.com/v4/letter/s/8e8cbc/32.png) [@Softmind\_Technology](https://rubytalk.org/u/Softmind_Technology)\
**Post date:** [26 January 2008 05:01 UTC](https://rubytalk.org/t/activerecord-connect-to-sqlserver/44018/7 "2008-01-26T05:01:48Z")

</div>

@ DVN

Thanks for this thread and thanks to others who helped him.

I would request you to kindly paste here the complete configuration file  
( YAML) along with the SQL driver installation and its location.

Your single file with full details shall help many many developers, who  
are facing same problem like you, including me.

Since the above codes are all mixed up, a single paste with full details  
shall be an wonderful idea.

I hope you will help us.

Thanks

SoftMind.

> **···**
>
> --  
> Posted via [http://www.ruby-forum.com/](http://www.ruby-forum.com/).
