Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Coldfusion Administrator will not register a SQL 2000 datasource.

Posted on 2003-11-08
3
Medium Priority
?
260 Views
Last Modified: 2013-12-24
I hope someone is up and can help me out real quick.  I am trying to convert my site from sybase to MS SQL 2000.  I can only get my administrator to connect to the database with the sa user and no other user I have created.  What am I doing wrong??  Also, how do I address calling the tables in the query when I do get it connected??
0
Comment
Question by:benjam222
3 Comments
 
LVL 9

Accepted Solution

by:
shooksm earned 2000 total points
ID: 9709046
Are you using mixed authentication?  That is both SQL server accounts and Windows Authentication to validate users.  If that is the case, make sure you are not trying to login with a Windows account.  If you need to use windows authentication, then I think you will have to use the same windows account used to run the Cold Fusion service and give it rights in SQL.

Another thing is to check the login.  Use query analyzer and see if you can connect to the server, select a database and run a simple query.

There are several steps in creating logins and giving the login the proper access to do anything.

1. Create a new user
2. Select the authentication mode
3. Fill in the password if necassary
4. Select the default database for the account
5. Select the third tab.
6. Check the permit box for the default database
7. Select the database
8. Select an appropriate role for the user like datareader and datawriter
9. Click OK to save

As for how to you call specific tables, there are a couple of ways.  First, the accounts default database will be used when you don't specify an alternate database in your CFQUERY tag.  So, you can get away with accessing the tables directly IE:

SELECT *
FROM SomeTableInMyDefaultDatabase

Second, you can specify the database to use in the database parameter of the CFQUERY tag.  Third, you can use fully qualified names to choose a table outside of your current context. IE:

SELECT *
FROM SomeOtherDatabase.dbo.SomeTableInAnotherDatabase

The fully qualified syntax is

SERVER.DATABASE.OWNER.OBJECT


Hope this helps
0
 
LVL 4

Expert Comment

by:procept
ID: 9709419
Hi,

in SQL Server you need to assign the specific user rights to use the database and maybe even rights to SELECT, INSERT, UPDATE, and DELETE receords.

In Enterprise Manager, expand the SQL Server Registration and click on "Security".

If your user is not listed in there, create a new one by right clicking in the window and choosing "New user". Assign that user a name, set his authentication to SQL Server authentication and type in his password. Click OK and re-enter his password.

Double click on the user name you wish to use, click on the "Database access" tab and make sure that all databases he should have access to are checked. Click OK.

Now the user has access to the database. Next you need to give him permissions to select, insert, update, and delete records. You can do this manually in Enterprise Manager (which actually is easy, but, takes long if you have many tables), or, you can use the stored procedure "sp_MSforeachtable" that comes with SQL Server.

Execute this command as sa in Query Analyzer (make sure you are connected to the correct database!):
EXECUTE sp_MSforeachtable 'GRANT SELECT ON ? TO your_usename_here'

Now you can use this user and his password to use the DB with CF.

HTH,

CHris

0
 
LVL 17

Expert Comment

by:anandkp
ID: 9713080

If u have got the logins & roles defined properly in MS SQL 2K - then u shld be able to connect.

chk ur settings in MS SQL 2K & try again - u shld be able to connect thru all users [else whats the point in creating them :)]

K'Rgds
Anand
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Turn A Profile Picture Into A Cartoon Using Photoshop And Illustrator This tutorial will teach you how to make a cartoon style image out of a regular picture. I have tried to keep the tutorial as simple as possible. I used Adobe CS4 for this tuto…
What You Need to Know when Searching for a Webhost Provider
The purpose of this video is to demonstrate how to insert an Iframe into WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Open Page or Post…
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question