Connect to database problem

Hi everyone,
I have installed MS SQL Server desktop on a PC running XPPRO
I am logged in as Administrator and have write permissions on all folders on the PC but
when i try to connect to the SQL database, (from within ASP.NET Web matrix an error message pops up
"Unable to connect to database server
SQL server does not exist or access is denied.
ConnectionOpen (Connect())"
(I have disabled Nortons AV just in case)
I am completely stumped on this one and any help would be most appreciated
Phil
Can we see your connection string (please obfuscate any user name/password info)?
This is a local SQL database on your machine, correct? Verifythat SQL Server is running; check the SQL Server Service Manager inyour systray and verify that it says running.
Verify the name of your SQL server by shelling out to a command prompt and typing:
osql -L
|||Thanks for your suggestions Terri.
Yes SQL server is running on my machine - it starts up automatically when I log on.
I dont have a database as yet (I haven't got that far!)
I am trying to teach myself ASP.NET on a work PC which is normally networked but I am running it as a stand alone PC with networking disabled and logged on to the PC rather than the network.
At the command prompt the OSQL -L command that you suggested returns "local"
When I was installing the MSDE software I ran the setup file at the command prompt as follows
setup SAPWD=mypassword SEQURITYMODE=SQL
I don't know what the connection string is but I am trying to connect using ASP.NET Web Matrix.
In the ASP.NET Web Matrix connection window dialogue it gives the server as localhost (I have also tried "local" without success)
I select "SQL server authentication" and give
Username sa
Passwordmypassword
I then choose "Create a new database" from the bottom left of the connection window dialogue and enter a name after which i get the error popup
"Unable to connet to database server
Server does not exist or access denied.
ConnectionOpen (connect())"
Back at the command prompt I have tried ...
osql -S -U sa -Pmypassword
and this seems to work in that the DOS prompt dissappears and is replaced by digits (exit gets me back to the dos prompt)
Again I would be most grateful for any suggestions to help me get past this brick wall!
Thanks
Phil
|||This is important. local must be enclosed in parentheses, like this:
(local)
So, choose (local) as your database, and <gulp> sa as your User name and mypassword as your password. SeeDISCLAIMER!
DISCLAIMER: Never use the saaccount for applications! We are just using the sa account hereto prove to yourselves that you can connect to your SQL Server and yourdatabase. This MUST be changed!!

|||

Many many thanks Tim.

I am in with one big sigh of relief from Tasmania!!
and can now continue with the remainder of my ASP.NET training.
Although I am only using MSDE for training purposes for the moment it sounds as though I need to add a new user (other than sa).
Do I need to run setup again?
Cheers
Phil

Connect SQL Server to Oracle 9i

I need to connect SQL Server 2000 to an Oracle database on another network for which we have permissions. The Oracle tech (they have no dba...a vendor set all of this up) created a schema for us and now I need to access it. They have provided a username and a password.

for example:

uname = test
pwd = testpwd
oracle box = ias4_192.x.x.x

In SQL Server how do I make this happen? Do I need to set up an ODBC connection, sp_addlinkedserver, etc. etc. I posted to another forum and was told something about a tnsnames.ora file and possibly a listener.ora file?!? Both the Oracle guy and myself are complete idiots when it comes to this kinda stuff. We each vaguely understand our own system but this heterogenous stuff is killing us! Please help. We need a step-by-step outline to make this happen. Any help would be GREATLY appreciated. Thanks!In order to connect the SQL Server via sp_addlinkedserver, you need to establish connectivity to the Oracle server.

You need to install the Oracle drivers onto the SQL Server (do a minimum install, just the drivers and the network connectivity tools).

Then you need to open your Oracle Network assistant and configure the connection settings (hostname or ip address and port number). You will need to give these settings a name (called a Net Service Name, I think).

Once these steps are complete, then you can use sp_addlinkedserver to specify the correct settings for SQL Server to "see" the Oracle server. You can also add it through the graphical utility in SQL's Enterprise Manager (under Security/Linked Servers). This latter method may be a bit easier for novices.

Please note, these are the steps that I have used for both Oracle 7.x and Oracle 8.x. They may be slightly different for 9i.

HTH,

hmscott

Confusing Cross-Database Permissions Issue

We're trying to follow the principle of least privilege here in setting up a user account for our website to use to access SQL Server 2005, but we're having a nightmarish time getting it to work.

The issue seems to be trying to get a limited access user account the ability to cross databases.

Here's the situation:

We have a User [WebUser] that we want to grant access to the database. This account has a login [WebUser] that has username=WebUser and password=ALongPassword.

This user only calls stored procedures in the database [WebData].

However, some of the stored procedures in [WebData] call stored procedures in the database [dbutil].

One of the stored procedures in [dbutil] inserts records into a table in a third database [dbutil_temp].[DebugLog].

This all works out great from my development account using Windows Authentication.

But as you might guess, if I do something like "EXECUTE AS [WebUser]" and run the same procedure on [WebData] things fall apart quickly. I've looked online regarding cross-database ownership chaining, but quite frankly, the whole users/logins/roles/schemas security model is confusing, and I'm getting nowhere fast on my own.

We really only want [WebUser] to have CONNECT and EXECUTE permissions on the primary [WebData] database, but it seems like we've got to do a lot more than that to get this to work.

I'd appreciate any help...

Yes, you have to do a bit more work, but it isn't a whole lot more work. The most appropriate solution for this is to use signatures for the cross database access. There are detailed demos for creating signatures as well as specifically signatures for cross database queries in the following blogs, which I VERY highly recommend reading.

http://blogs.msdn.com/lcris/

http://blogs.msdn.com/raulga/

I would also suggest listenting to the security presentations at http://cmcgc.com/media/WMP/261115

|||Thanks Mike, I'll take a look at those resources.

Conflict of Permissions by NT Groups

Hi,

This is regarding permission issue in windows-authenticated sql server 2000.

I have two NT groups namely A & B.

Groups A has all permissions on SQLDB1 while group B has all on SQLDB1 and SQLDB2.

Since I dont want B to have INS/UPD/DEL rights on SQLDB1, I revoked those permissions for B on the same. But users belonging to both groups suffer INS/UPD/DEL rights on SQLDB1.

Could anyone help please ?

Thanks in advance.

Use DENY instead of REVOKE.

--

Tibor Karaszi, SQL Server MVP

http://www.karaszi.com/sqlserver/default.asp

http://www.solidqualitylearning.com/

Blog: http://solidqualitylearning.com/blogs/tibor/

wrote in message

news:2d0faecd-406e-4bcd-9aba-70fc886637ce@.discussions.microsoft.com...

> Hi,

>

> This is regarding permission issue in windows-authenticated sql server

> 2000.

>

> I have two NT groups namely A & B.

>

> Groups A has all permissions on SQLDB1 while group B has all on SQLDB1

> and SQLDB2.

>

> Since I dont want B to have INS/UPD/DEL rights on SQLDB1, I revoked

> those permissions for B on the same. But users belonging to both groups

> suffer INS/UPD/DEL rights on SQLDB1.

>

> Could anyone help please ?

>

> Thanks in advance.

>

>|||

I tried DENY too. Result is the same as before.

|||

Can you explain in more detail what commands you executed? Also, can you check the catalog to verify that permissions are granted/denied as intended. The database permissions catalog is sys.database_permissions.

Thanks
Laurentiu

|||There is no such a table called sys.database_permissions in sql server 2000. Thanks.|||

In SQL 2000 you can use sysprotects. See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-p_0837.asp, for a description.

Thanks
Laurentiu

Conflict of Permissions by NT Groups

Hi,

This is regarding permission issue in windows-authenticated sql server 2000.

I have two NT groups namely A & B.

Groups A has all permissions on SQLDB1 while group B has all on SQLDB1 and SQLDB2.

Since I dont want B to have INS/UPD/DEL rights on SQLDB1, I revoked those permissions for B on the same. But users belonging to both groups suffer INS/UPD/DEL rights on SQLDB1.

Could anyone help please ?

Thanks in advance.

Use DENY instead of REVOKE.

--

Tibor Karaszi, SQL Server MVP

http://www.karaszi.com/sqlserver/default.asp

http://www.solidqualitylearning.com/

Blog: http://solidqualitylearning.com/blogs/tibor/

wrote in message

news:2d0faecd-406e-4bcd-9aba-70fc886637ce@.discussions.microsoft.com...

> Hi,

>

> This is regarding permission issue in windows-authenticated sql server

> 2000.

>

> I have two NT groups namely A & B.

>

> Groups A has all permissions on SQLDB1 while group B has all on SQLDB1

> and SQLDB2.

>

> Since I dont want B to have INS/UPD/DEL rights on SQLDB1, I revoked

> those permissions for B on the same. But users belonging to both groups

> suffer INS/UPD/DEL rights on SQLDB1.

>

> Could anyone help please ?

>

> Thanks in advance.

>

>|||

I tried DENY too. Result is the same as before.

|||

Can you explain in more detail what commands you executed? Also, can you check the catalog to verify that permissions are granted/denied as intended. The database permissions catalog is sys.database_permissions.

Thanks
Laurentiu

|||There is no such a table called sys.database_permissions in sql server 2000. Thanks.|||

In SQL 2000 you can use sysprotects. See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-p_0837.asp, for a description.

Thanks
Laurentiu