|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
This sure is cornfusing ... connection woesAttached 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? 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. -- Show quoteGregory A. Beamer MVP; MCP: +I, SE, SD, DBA ************************************************* Think Outside the Box! ************************************************* "Earl" <brikshoe@newsgroups.nospam> wrote in message news:eDeVX1BvGHA.4512@TK2MSFTNGP05.phx.gbl... > 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? > 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). Show quote "msnews.microsoft.com" <NoSpamMgbworld@comcast.netNoSpamM> wrote in message news:%23BR412BvGHA.4160@TK2MSFTNGP06.phx.gbl... > 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" <brikshoe@newsgroups.nospam> wrote in message > news:eDeVX1BvGHA.4512@TK2MSFTNGP05.phx.gbl... >> 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? >> > > 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. Show quote "msnews.microsoft.com" <NoSpamMgbworld@comcast.netNoSpamM> wrote in message news:%23BR412BvGHA.4160@TK2MSFTNGP06.phx.gbl... > 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" <brikshoe@newsgroups.nospam> wrote in message > news:eDeVX1BvGHA.4512@TK2MSFTNGP05.phx.gbl... >> 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? >> > > 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. -- Show quoteGregory A. Beamer MVP; MCP: +I, SE, SD, DBA ************************************************* Think Outside the Box! ************************************************* "Earl" <brikshoe@newsgroups.nospam> wrote in message news:eqI$WeCvGHA.1372@TK2MSFTNGP04.phx.gbl... > 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. > > "msnews.microsoft.com" <NoSpamMgbworld@comcast.netNoSpamM> wrote in > message news:%23BR412BvGHA.4160@TK2MSFTNGP06.phx.gbl... >> 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" <brikshoe@newsgroups.nospam> wrote in message >> news:eDeVX1BvGHA.4512@TK2MSFTNGP05.phx.gbl... >>> 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? >>> >> >> > > 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. Show quote "msnews.microsoft.com" <NoSpamMgbworld@comcast.netNoSpamM> wrote in message news:Og$ve1CvGHA.4972@TK2MSFTNGP05.phx.gbl... > 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! > ************************************************* > "Earl" <brikshoe@newsgroups.nospam> wrote in message > news:eqI$WeCvGHA.1372@TK2MSFTNGP04.phx.gbl... >> 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. >> >> "msnews.microsoft.com" <NoSpamMgbworld@comcast.netNoSpamM> wrote in >> message news:%23BR412BvGHA.4160@TK2MSFTNGP06.phx.gbl... >>> 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" <brikshoe@newsgroups.nospam> wrote in message >>> news:eDeVX1BvGHA.4512@TK2MSFTNGP05.phx.gbl... >>>> 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? >>>> >>> >>> >> >> > > 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? Show quote "Earl" <brikshoe@newsgroups.nospam> wrote in message news:eqI$WeCvGHA.1372@TK2MSFTNGP04.phx.gbl... > 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. > > "msnews.microsoft.com" <NoSpamMgbworld@comcast.netNoSpamM> wrote in > message news:%23BR412BvGHA.4160@TK2MSFTNGP06.phx.gbl... >> 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" <brikshoe@newsgroups.nospam> wrote in message >> news:eDeVX1BvGHA.4512@TK2MSFTNGP05.phx.gbl... >>> 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? >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics