locked
how to change database to sql server authentication RRS feed

  • Question

  • hello,

    i have a sql server 2008 express database in my web development environment that uses windows authentication via visual studio 2010.  i now want to upload the database to a hosting provider, but they only support sql server authentication.  how do i change the database authentication to sql server authentication?

    my sql server management studio is already configured for "sql server and windows authentication mode."

    thanks!

    Wednesday, December 5, 2012 1:29 AM

Answers

  • Hello,

    The Authentification Mode is a server setting, not a database setting. Windows authentifiction can only be used in a windows domain (ADS) enviroment or on a local machine; the web host isn't in your domain, therefore you can use Windows authentification and so your provider changed the mode to mixed mode to allow SQL Logins.

    And this means you have to create a SQL login on the hosted SQL Server, add it as database user in your database and grant permissions for it.


    Olaf Helper

    Blog Xing

    • Proposed as answer by Rama Udaya Thursday, December 6, 2012 3:05 AM
    • Marked as answer by desertchief Friday, December 7, 2012 7:18 AM
    Wednesday, December 5, 2012 7:03 AM
  • I repeat what Olaf says: the authentication mode is a server setting not a database setting.

    As for deploying the database, it is true that this is a subject for a better forum. However, I can mention a few options.

    Many people use SQL Compare from Red Gate and are happy with that. If you also need to synchronise data, they also have SQL Data Compare.

    Microsoft offers SQL Server Data Tools that is a free add-in to Visual Studio and supports SQL 2005 and up.

    The Web Deploy tool you mention appears to have a wider scope than just the database. Maybe you need that, but then we are really outside the scope for this forum. :-)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by desertchief Friday, December 7, 2012 7:18 AM
    Thursday, December 6, 2012 10:33 PM

All replies

  • http://msdn.microsoft.com/en-us/library/ms188670.aspx

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, December 5, 2012 6:25 AM
  • thank you and correct me if i am wrong, but this link is to change the SQL SERVER authentication mode.  i have a database in windows authentication mode that i need to change to sql server authentication mode.  that is, i need to change the database so that it accepts a sql username and password.
    Wednesday, December 5, 2012 6:49 AM
  • hi,

    In this case you can connect sql server using sqlcmd

    sqlcmd -s(ipofserver) -E (login using windows authentication)

    once u r connected then execute below query

    ALTER LOGIN sa ENABLE ;
    GO
    ALTER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>' ;
    GO

    Wednesday, December 5, 2012 6:52 AM
  • Hello,

    The Authentification Mode is a server setting, not a database setting. Windows authentifiction can only be used in a windows domain (ADS) enviroment or on a local machine; the web host isn't in your domain, therefore you can use Windows authentification and so your provider changed the mode to mixed mode to allow SQL Logins.

    And this means you have to create a SQL login on the hosted SQL Server, add it as database user in your database and grant permissions for it.


    Olaf Helper

    Blog Xing

    • Proposed as answer by Rama Udaya Thursday, December 6, 2012 3:05 AM
    • Marked as answer by desertchief Friday, December 7, 2012 7:18 AM
    Wednesday, December 5, 2012 7:03 AM
  • Create a new user with required privileges with a strong password.

    Use these credentials to connect to your database. When you connect to your application from visual studio use this userid and password to establish connection.

    Wednesday, December 5, 2012 9:39 PM
  • Take a look at this web site how to use connection to the database with Mixed or Windows Authentication

    http://www.connectionstrings.com/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thursday, December 6, 2012 6:03 AM
  • thanks to everyone and the conectionstrings.com is a good reference, but i think i have a bigger problem. 

    it looks like i need to keep the local database in windows authentication mode, then upload the database to the hosting provider using VS2010's "Web Deploy" located on the "Publish" toolbar.  i tried this option several times using various Service URLs and Site/application names with no luck.  i now find out that the provider does not support the Web Deploy tool in VS2010.  only the FTP option seems to work.  this is a temporary solution because it becomes tedious converting scripts every time i want to test a new database iteration.

    this is the link i found with some guidance: http://weblogs.asp.net/scottgu/archive/2010/09/13/automating-deployment-with-microsoft-web-deploy.aspx

    i am still looking for a better option, but i think that might be a question for a different forum group.

    Thursday, December 6, 2012 6:44 AM
  • it looks like i need to keep the local database in windows authentication mode,

    Again: It's not a database setting.

    Olaf Helper

    Blog Xing

    Thursday, December 6, 2012 7:34 AM
  • I repeat what Olaf says: the authentication mode is a server setting not a database setting.

    As for deploying the database, it is true that this is a subject for a better forum. However, I can mention a few options.

    Many people use SQL Compare from Red Gate and are happy with that. If you also need to synchronise data, they also have SQL Data Compare.

    Microsoft offers SQL Server Data Tools that is a free add-in to Visual Studio and supports SQL 2005 and up.

    The Web Deploy tool you mention appears to have a wider scope than just the database. Maybe you need that, but then we are really outside the scope for this forum. :-)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by desertchief Friday, December 7, 2012 7:18 AM
    Thursday, December 6, 2012 10:33 PM
  • thank you erland and olaf for the clarification.  for someone with little sql experience viewing the problem from the user(web developer) side, it looks like a database problem when one tries to upload to a hosting provider with just sql login support.

    my core problem is to find a best practices method to upload and sync my databases (from local development machine to hosting provider) with minimal effort.  a single click would be ideal.  my current method is via ftp and sql scripts, and this time consuming. 

    reg gate looks promising, and i need to do some reading on the microsoft tools. 

    thanks!

    Friday, December 7, 2012 6:56 AM