Coldfusion Administrator will not register a SQL 2000 datasource.

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??
benjam222Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

shooksmCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
proceptCommented:
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
anandkpCommented:

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.