Solved

Add user to MSSQL server 2012  for the web user linked to coldfusion

Posted on 2014-10-03
9
168 Views
Last Modified: 2014-10-11
I have migrated from SQL Server 2005 to SQL server 2012 and CF11.  A user was set up as the user in coldfusion in my old setup - that is the web visitor. I dont know the password for the old web user set up. I am a novice and this is a once in 12 years  for me -  SQL Server 2012 seems complex. I just want to add a user for the CF (web visitor) and it asks for a password. Cant even see where you add a password to the user. Anyhow appreciate some help So the webuser can read - write to db - register, update etc.

Thanks
0
Comment
Question by:Ian White
  • 5
  • 4
9 Comments
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
What is asking for a password - SQL Server or ColdFusion? Might be easier to post a screen shot.

Also, did you upgrade - or are these completely new installs?
0
 

Author Comment

by:Ian White
Comment Utility
I upgraded from SQL server 2005 to 2012 via 2008 - migrating to a new host

Coldfusion needs a username and password for the public user (surfer) to login - it is where you define, verify the
DSN for the database. The user needs read and write access

Does anyone have the Transactional SQL so I can try and create a new public user with read and write access (not admin). Sorry could not do screen print from RDT - but here is a copy of labels on CF admin page

Data & Services > Datasources > Microsoft SQL Server
 
 Microsoft SQL Server :  housecare2  
 CF Data Source Name      
 Database    
 Server        Port      
 User name    
 Password    
 Description
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
> Coldfusion needs a username and password for the public user

EDIT: Yes, I understand but it's not clear whether you really need to create a new user account.  That should only be necessary if it's completely new db.  If you upgraded an existing db, it would still contain all the old data AND logins.  So you wouldn't need to a new login account at all.  Just to reset the password of the old one.

If that's the case, just log into the SQL Server database with something like Management Studio.  Locate the login and change the password to something else.  It can also be done with TSQL:

      ALTER LOGIN surfer WITH PASSWORD = 'the new password you want here';

Then use the new credentials when creating the DSN in the CF Administrator.
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
> Does anyone have the Transactional SQL so I can try and create a new public user with read and write access (not admin).

I normally use SSMS for managing logins, instead of TSQL. So I'll let someone else offer up the TSQL.  But if you're interested in using SSMS it looks like 2012 hasn't changed much since 2008:

Add new login

1. Open Management Studio. Select Object Explorer > Security
2. Right click Logins > Add New
3. Enter the desired user name. Check "SQL Server Authentication" and enter a password
* Note: For web application accounts, you usually want to uncheck the last two options, ie "expire" and "change at next login"
create login and password4. Without closing the window, select "User Mapping" from the left menu. Check the box next to the name of your web database.
5. Under "Database role membership", check the boxes for the desired roles. Then click OK to apply.

* Note: For DEV db's I often use "db_owner".  For PROD you may want to assign different roles like db_datareader, db_datawriter, etc... All depends on what permissions your app needs.

assign login roleWhen done, verify the new password works in SQL Server. Then enter the new credentials in your CF DSN.

HTH
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:Ian White
Comment Utility
No 2012 has changed .  It is right click on user then add new user - then SQL user with login - it asks for username and login name -    and there is no provision to put in a password.

I altered an old login password and it excecuted - then when I tried to add to coldfusion dsn verification I got
Connection verification failed for data source: housecare2
java.sql.SQLInvalidAuthorizationSpecException: [macromedia][SQLServer JDBC Driver][SQLServer]Login failed for user 'house-pub'.
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
I've gotten that error when updating an existing DSN -after changing the password. As if the credentials were cached somehow. It didn't stick until I edited the DSN a 2nd or 3rd time (changing the password - then clicking submit)

If it's a brand new DSN:

1. Did you verify you've enabled SQL Server Authentication in the db? Mixed mode authentication is NOT enabled by default.
2. Did you verify the credentials in SQL Server 1st?
0
 

Accepted Solution

by:
Ian White earned 0 total points
Comment Utility
Thanks. I finally got a response from the new hosting company. So it appears it something you need to be aware of when migrating? The DSN works now after below. It appears the login lives below the user - 2 entities.

In this case the issue was related to the "house-pub" user that was restored when the database was migrated onto the VPS server. Since the MSSQL server and MSSQL databases maintain their own user accounts and login, it turned out that the "house-pub" user that was created for the MSSQL server, was not linking to the "house-pub" user that was restored with the database. Hence when trying to access the database the login would fail. To correct this issue I ended up documenting the access rights granted to the "house-pub" user attached to the database, and removing all instances of the user account from the server, before adding it back and re-configuring the account with the proper user rights as you had previously setup.
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Ohh... that makes sense.  I'd forgotten about that. The same thing happened to me once when restoring a dump of another db into my DEV environment due to how SQL Server treats server level logins vs db users.  

Glad you solved it! Don't forget to mark your comment as the answer to close out this thread.
0
 

Author Closing Comment

by:Ian White
Comment Utility
Thanks for your contributions, as it helped me get to a solution
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

771 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