This sure is cornfusing ... connection woes

[Earl]

SQL2005. New installation as a named instance on a local machine.

Attached a database previously designed in SQL2000. My app calls for this
database using integrated security. With SQL2000, no problems connecting and
executing code against the db, including stored procedures.

But with 2005, right out of the gate, when I try to get connected from the
app I get error 4060: "Cannot open database requested in login [myDb]. Login
fails. Login failed for user '[local user with system admin permissions].

Well, I try to set the owner of the database to that user. I see in the user
mappings that my user is mapped to the database I am trying to connect to.
For whatever reason, a user mapped to a database cannot own that database.
Who woulda thunk? So I clear that mapping and set the user to be the owner
of the database.

Still cannot connect from the app. Still getting error 4060. Connection
string is of the form:
data source=SQLServerName;initial catalog=DatabaseName;integrated
security=SSPI;

When I am in the dbo Database User page, I see Owned Schemaas and Database
roles. I know what a role is but what the heck is an Owned schema?

I've run through Bill Vaugn's checklist, but no joy there. I've looked at
Sahil Malik's page and found nothing to solve this either.

Finally, I like the functioning of SQL2005, but as to configuration, why do
I feel like I'm looking at a Rube Goldberg contraption?

 

[msnews.microsoft.com]

Open the configuration tool and make sure you are able to connect with
something other than Native SQL (TCP/IP for example). Then, if it still has
a problem, turn on the SQL Browser.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think Outside the Box!
*************************************************

 

[Earl]

What I've done to test this is to install the app on my laptop by dropping
it in Xcopy to a folder (I'm trying to see this as the end-user would). On
the other hand, the dev machine is a desktop with SQL2k ONLY, but note that
the laptop has both 2000 and 2005. On the laptop, I am already using TCP/IP
only for local and remote connections (since this was the configuration I
used with SQL2k).

Open the configuration tool and make sure you are able to connect with
something other than Native SQL (TCP/IP for example). Then, if it still
has a problem, turn on the SQL Browser.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think Outside the Box!
*************************************************

SQL2005. New installation as a named instance on a local machine.

Attached a database previously designed in SQL2000. My app calls for this
database using integrated security. With SQL2000, no problems connecting
and executing code against the db, including stored procedures.

But with 2005, right out of the gate, when I try to get connected from
the app I get error 4060: "Cannot open database requested in login
[myDb]. Login fails. Login failed for user '[local user with system admin
permissions].

Well, I try to set the owner of the database to that user. I see in the
user mappings that my user is mapped to the database I am trying to
connect to. For whatever reason, a user mapped to a database cannot own
that database. Who woulda thunk? So I clear that mapping and set the user
to be the owner of the database.

Still cannot connect from the app. Still getting error 4060. Connection
string is of the form:
data source=SQLServerName;initial catalog=DatabaseName;integrated
security=SSPI;

When I am in the dbo Database User page, I see Owned Schemaas and
Database roles. I know what a role is but what the heck is an Owned
schema?

I've run through Bill Vaugn's checklist, but no joy there. I've looked at
Sahil Malik's page and found nothing to solve this either.

Finally, I like the functioning of SQL2005, but as to configuration, why
do I feel like I'm looking at a Rube Goldberg contraption?

 

[Earl]

I've just discovered something partially related to this issue. I use a
handful of typed datasets in my app and I've noticed that the data adapters
did NOT pick up the coded in connection string when submitting Inserts.

Open the configuration tool and make sure you are able to connect with
something other than Native SQL (TCP/IP for example). Then, if it still
has a problem, turn on the SQL Browser.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think Outside the Box!
*************************************************

SQL2005. New installation as a named instance on a local machine.

Attached a database previously designed in SQL2000. My app calls for this
database using integrated security. With SQL2000, no problems connecting
and executing code against the db, including stored procedures.

But with 2005, right out of the gate, when I try to get connected from
the app I get error 4060: "Cannot open database requested in login
[myDb]. Login fails. Login failed for user '[local user with system admin
permissions].

Well, I try to set the owner of the database to that user. I see in the
user mappings that my user is mapped to the database I am trying to
connect to. For whatever reason, a user mapped to a database cannot own
that database. Who woulda thunk? So I clear that mapping and set the user
to be the owner of the database.

Still cannot connect from the app. Still getting error 4060. Connection
string is of the form:
data source=SQLServerName;initial catalog=DatabaseName;integrated
security=SSPI;

When I am in the dbo Database User page, I see Owned Schemaas and
Database roles. I know what a role is but what the heck is an Owned
schema?

I've run through Bill Vaugn's checklist, but no joy there. I've looked at
Sahil Malik's page and found nothing to solve this either.

Finally, I like the functioning of SQL2005, but as to configuration, why
do I feel like I'm looking at a Rube Goldberg contraption?

 

[msnews.microsoft.com]

You want to pull the connection string from the configuration file, esp. if
this is going to be set up on an end user machine, where they might not use
(local), but rather, an instance.

The built in connection strings from drag and drop are designed for
debugging. You can take them beyond that level, but it is better to move the
actual data access to its own layer (possibly own library) and send the conn
string in when you ask for data. You then store it somewhere (config file is
easiest in most cases) and allow it to be configurable through some means.

Hope this helps.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think Outside the Box!
*************************************************

I've just discovered something partially related to this issue. I use a
handful of typed datasets in my app and I've noticed that the data
adapters did NOT pick up the coded in connection string when submitting
Inserts.

Open the configuration tool and make sure you are able to connect with
something other than Native SQL (TCP/IP for example). Then, if it still
has a problem, turn on the SQL Browser.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think Outside the Box!
*************************************************

SQL2005. New installation as a named instance on a local machine.

Attached a database previously designed in SQL2000. My app calls for
this database using integrated security. With SQL2000, no problems
connecting and executing code against the db, including stored
procedures.

But with 2005, right out of the gate, when I try to get connected from
the app I get error 4060: "Cannot open database requested in login
[myDb]. Login fails. Login failed for user '[local user with system
admin permissions].

Well, I try to set the owner of the database to that user. I see in the
user mappings that my user is mapped to the database I am trying to
connect to. For whatever reason, a user mapped to a database cannot own
that database. Who woulda thunk? So I clear that mapping and set the
user to be the owner of the database.

Still cannot connect from the app. Still getting error 4060. Connection
string is of the form:
data source=SQLServerName;initial catalog=DatabaseName;integrated
security=SSPI;

When I am in the dbo Database User page, I see Owned Schemaas and
Database roles. I know what a role is but what the heck is an Owned
schema?

I've run through Bill Vaugn's checklist, but no joy there. I've looked
at Sahil Malik's page and found nothing to solve this either.

Finally, I like the functioning of SQL2005, but as to configuration, why
do I feel like I'm looking at a Rube Goldberg contraption?

 

[Earl]

I have the connections set up in their own class, pulling from a pretty
simply xml file. Simply dropping the connection string from the properties
page and setting it in code for the data adapters did the trick. The other
part of the connection issue in the server was the permissions on the
database, which are apparently dropped by default when attaching a database.
Thanks for the ideas.

You want to pull the connection string from the configuration file, esp.
if this is going to be set up on an end user machine, where they might not
use (local), but rather, an instance.

The built in connection strings from drag and drop are designed for
debugging. You can take them beyond that level, but it is better to move
the actual data access to its own layer (possibly own library) and send
the conn string in when you ask for data. You then store it somewhere
(config file is easiest in most cases) and allow it to be configurable
through some means.

Hope this helps.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think Outside the Box!
*************************************************

I've just discovered something partially related to this issue. I use a
handful of typed datasets in my app and I've noticed that the data
adapters did NOT pick up the coded in connection string when submitting
Inserts.

Open the configuration tool and make sure you are able to connect with
something other than Native SQL (TCP/IP for example). Then, if it still
has a problem, turn on the SQL Browser.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think Outside the Box!
*************************************************
SQL2005. New installation as a named instance on a local machine.

Attached a database previously designed in SQL2000. My app calls for
this database using integrated security. With SQL2000, no problems
connecting and executing code against the db, including stored
procedures.

But with 2005, right out of the gate, when I try to get connected from
the app I get error 4060: "Cannot open database requested in login
[myDb]. Login fails. Login failed for user '[local user with system
admin permissions].

Well, I try to set the owner of the database to that user. I see in the
user mappings that my user is mapped to the database I am trying to
connect to. For whatever reason, a user mapped to a database cannot own
that database. Who woulda thunk? So I clear that mapping and set the
user to be the owner of the database.

Still cannot connect from the app. Still getting error 4060. Connection
string is of the form:
data source=SQLServerName;initial catalog=DatabaseName;integrated
security=SSPI;

When I am in the dbo Database User page, I see Owned Schemaas and
Database roles. I know what a role is but what the heck is an Owned
schema?

I've run through Bill Vaugn's checklist, but no joy there. I've looked
at Sahil Malik's page and found nothing to solve this either.

Finally, I like the functioning of SQL2005, but as to configuration,
why do I feel like I'm looking at a Rube Goldberg contraption?

 

[Earl]

As an aside here, it's not causing any issues, but I've noticed that the
WorkstationID remains in the properties page even with the connection string
taken out. Any way to remove that?

I've just discovered something partially related to this issue. I use a
handful of typed datasets in my app and I've noticed that the data
adapters did NOT pick up the coded in connection string when submitting
Inserts.

Open the configuration tool and make sure you are able to connect with
something other than Native SQL (TCP/IP for example). Then, if it still
has a problem, turn on the SQL Browser.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think Outside the Box!
*************************************************

SQL2005. New installation as a named instance on a local machine.

Attached a database previously designed in SQL2000. My app calls for
this database using integrated security. With SQL2000, no problems
connecting and executing code against the db, including stored
procedures.

But with 2005, right out of the gate, when I try to get connected from
the app I get error 4060: "Cannot open database requested in login
[myDb]. Login fails. Login failed for user '[local user with system
admin permissions].

Well, I try to set the owner of the database to that user. I see in the
user mappings that my user is mapped to the database I am trying to
connect to. For whatever reason, a user mapped to a database cannot own
that database. Who woulda thunk? So I clear that mapping and set the
user to be the owner of the database.

Still cannot connect from the app. Still getting error 4060. Connection
string is of the form:
data source=SQLServerName;initial catalog=DatabaseName;integrated
security=SSPI;

When I am in the dbo Database User page, I see Owned Schemaas and
Database roles. I know what a role is but what the heck is an Owned
schema?

I've run through Bill Vaugn's checklist, but no joy there. I've looked
at Sahil Malik's page and found nothing to solve this either.

Finally, I like the functioning of SQL2005, but as to configuration, why
do I feel like I'm looking at a Rube Goldberg contraption?