Solved

Coldfusion Administrator will not register a SQL 2000 datasource.

Posted on 2003-11-08
3
237 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 500 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Now that Expression Web 4.0 (http://www.microsoft.com/expression/products/Upgrade.aspx) is free if you buy or have the full version of Expression Web 3.0, now is the best time to  migrate from FrontPage to Expression Web (http://www.frontpage-to-exp…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
The purpose of this video is to demonstrate how to connect a WordPress website to Google Analytics. 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 :…
The purpose of this video is to demonstrate how to add AdSense Ads to a WordPress Website, and how to set up WordPress to automatically place Ads in Sidebars. This will be demonstrated using a Windows 8 PC. Log into your AdSense account. : Cli…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now