SQL Server connection string for deployment ?

Discussion in 'Microsoft Dot NET' started by UsenetUser, Aug 7, 2009.

  1. UsenetUser

    UsenetUser Guest

    VS 2005 clickonce deployment: What connection string do I use when I
    deploy a windows forms application with an SQL Server DB? If I use sa
    with no password, I get an error: login failed for user 'sa' Not
    associated with a trusted SQL server connection. If I use integrated
    security it fails, as I expect it would, because the user is unkown.

    Thank you.
     
    UsenetUser, Aug 7, 2009
    #1
    1. Advertisements

  2. UsenetUser

    sloan Guest

    You need a mental overhaul if you think "sa" is a production-end-user
    username (and password).
    You NEVER give out the sa password. Only a handful of people should even
    know what the sa password is.



    First off, do you want to use sql authentication or windows integrated
    authentication?

    Second, bookmark www.connectionstrings.com

    Here is some code based on windows authentication.

    Look up each of the "sp_" calls if you want to learn more.
    And google "sql vs windows authentication" "sql server"



    This is Sql Server 2005 code, but will probably run on 2000,2008 as well.












    declare @databaseName varchar(64)
    select @databaseName = db_name()
    declare @currentLoginName varchar(64)

    print '/@databaseName/'
    print @databaseName
    print ''


    select @currentLoginName = 'mydomain\user1'

    if not exists (select null from master.dbo.syslogins where isntname = 1 and
    loginname = @currentLoginName )
    begin
    EXEC sp_grantlogin @loginame = @currentLoginName
    EXEC sp_defaultdb @loginame = @currentLoginName , @defdb = @databaseName
    end


    select @currentLoginName = 'mydomain\user2'

    if not exists (select null from master.dbo.syslogins where isntname = 1 and
    loginname = @currentLoginName )
    begin
    EXEC sp_grantlogin @loginame = @currentLoginName
    EXEC sp_defaultdb @loginame = @currentLoginName , @defdb = @databaseName
    end


    select @currentLoginName = 'mydomain\user3'

    if not exists (select null from master.dbo.syslogins where isntname = 1 and
    loginname = @currentLoginName )
    begin
    EXEC sp_grantlogin @loginame = @currentLoginName
    EXEC sp_defaultdb @loginame = @currentLoginName , @defdb = @databaseName
    end



    select @currentLoginName = 'mydomain\user4'

    if not exists (select null from master.dbo.syslogins where isntname = 1 and
    loginname = @currentLoginName )
    begin
    EXEC sp_grantlogin @loginame = @currentLoginName
    EXEC sp_defaultdb @loginame = @currentLoginName , @defdb = @databaseName
    end


    select @currentLoginName = 'mydomain\user5'

    if not exists (select null from master.dbo.syslogins where isntname = 1 and
    loginname = @currentLoginName )
    begin
    EXEC sp_grantlogin @loginame = @currentLoginName
    EXEC sp_defaultdb @loginame = @currentLoginName , @defdb = @databaseName
    end



    select @currentLoginName = 'mydomain\user6'

    if not exists (select null from master.dbo.syslogins where isntname = 1 and
    loginname = @currentLoginName )
    begin
    EXEC sp_grantlogin @loginame = @currentLoginName
    EXEC sp_defaultdb @loginame = @currentLoginName , @defdb = @databaseName
    end


    --==========================









    declare @sp_grantdbaccess_return_value int
    declare @sp_grantdbaccess_return_value_total int
    select @sp_grantdbaccess_return_value_total = 0

    -- declare @currentLoginName varchar(64)



    select @currentLoginName = 'mydomain\user1'
    if not exists (select * from master.dbo.syslogins ml inner join dbo.sysusers
    su on ml.sid = su.sid where ml.name = @currentLoginName and su.uid <
    6382 )
    begin
    exec @sp_grantdbaccess_return_value = sp_grantdbaccess @currentLoginName
    select @sp_grantdbaccess_return_value_total =
    @sp_grantdbaccess_return_value_total + @sp_grantdbaccess_return_value
    end

    select @currentLoginName = 'mydomain\user2'
    if not exists (select * from master.dbo.syslogins ml inner join dbo.sysusers
    su on ml.sid = su.sid where ml.name = @currentLoginName and su.uid <
    6382 )
    begin
    exec @sp_grantdbaccess_return_value = sp_grantdbaccess @currentLoginName
    select @sp_grantdbaccess_return_value_total =
    @sp_grantdbaccess_return_value_total + @sp_grantdbaccess_return_value
    end

    select @currentLoginName = 'mydomain\user3'
    if not exists (select * from master.dbo.syslogins ml inner join dbo.sysusers
    su on ml.sid = su.sid where ml.name = @currentLoginName and su.uid <
    6382 )
    begin
    exec @sp_grantdbaccess_return_value = sp_grantdbaccess @currentLoginName
    select @sp_grantdbaccess_return_value_total =
    @sp_grantdbaccess_return_value_total + @sp_grantdbaccess_return_value
    end


    select @currentLoginName = 'mydomain\user4'
    if not exists (select * from master.dbo.syslogins ml inner join dbo.sysusers
    su on ml.sid = su.sid where ml.name = @currentLoginName and su.uid <
    6382 )
    begin
    exec @sp_grantdbaccess_return_value = sp_grantdbaccess @currentLoginName
    select @sp_grantdbaccess_return_value_total =
    @sp_grantdbaccess_return_value_total + @sp_grantdbaccess_return_value
    end

    select @currentLoginName = 'mydomain\user5'
    if not exists (select * from master.dbo.syslogins ml inner join dbo.sysusers
    su on ml.sid = su.sid where ml.name = @currentLoginName and su.uid <
    6382 )
    begin
    exec @sp_grantdbaccess_return_value = sp_grantdbaccess @currentLoginName
    select @sp_grantdbaccess_return_value_total =
    @sp_grantdbaccess_return_value_total + @sp_grantdbaccess_return_value
    end




    print '/@sp_grantdbaccess_return_value_total/'
    print @sp_grantdbaccess_return_value_total








    -- declare @currentLoginName varchar(64)

    declare @sp_addrolemember_return_value int
    declare @sp_addrolemember_return_value_total int
    select @sp_addrolemember_return_value_total = 0


    declare @currentRole varchar(64)
    --select @currentRole = 'db_owner'
    select @currentRole = 'db_datareader'




    select @currentLoginName = 'mydomain\user2'
    if not exists
    (
    SELECT null --, role_principal_id, member_principal_id, princ1.name AS
    role_name, princ2.name As member_name
    FROM sys.database_role_members AS S LEFT JOIN sys.database_principals AS
    princ1 ON S.role_principal_id = princ1.principal_id AND princ1.type = 'R'
    LEFT JOIN sys.database_principals AS princ2 ON S.member_principal_id =
    princ2.principal_id AND princ2.type IN ('S', 'U')
    WHERE princ1.name = @currentRole and princ2.name = @currentLoginName
    )
    begin
    exec sp_addrolemember @currentRole, @currentLoginName
    end


    select @currentLoginName = 'mydomain\user3'
    if not exists
    (
    SELECT null --, role_principal_id, member_principal_id, princ1.name AS
    role_name, princ2.name As member_name
    FROM sys.database_role_members AS S LEFT JOIN sys.database_principals AS
    princ1 ON S.role_principal_id = princ1.principal_id AND princ1.type = 'R'
    LEFT JOIN sys.database_principals AS princ2 ON S.member_principal_id =
    princ2.principal_id AND princ2.type IN ('S', 'U')
    WHERE princ1.name = @currentRole and princ2.name = @currentLoginName
    )
    begin
    exec sp_addrolemember @currentRole, @currentLoginName
    end

    select @currentLoginName = 'mydomain\user4'
    if not exists
    (
    SELECT null --, role_principal_id, member_principal_id, princ1.name AS
    role_name, princ2.name As member_name
    FROM sys.database_role_members AS S LEFT JOIN sys.database_principals AS
    princ1 ON S.role_principal_id = princ1.principal_id AND princ1.type = 'R'
    LEFT JOIN sys.database_principals AS princ2 ON S.member_principal_id =
    princ2.principal_id AND princ2.type IN ('S', 'U')
    WHERE princ1.name = @currentRole and princ2.name = @currentLoginName
    )
    begin
    exec sp_addrolemember @currentRole, @currentLoginName
    end

    select @currentLoginName = 'mydomain\user5'
    if not exists
    (
    SELECT null --, role_principal_id, member_principal_id, princ1.name AS
    role_name, princ2.name As member_name
    FROM sys.database_role_members AS S LEFT JOIN sys.database_principals AS
    princ1 ON S.role_principal_id = princ1.principal_id AND princ1.type = 'R'
    LEFT JOIN sys.database_principals AS princ2 ON S.member_principal_id =
    princ2.principal_id AND princ2.type IN ('S', 'U')
    WHERE princ1.name = @currentRole and princ2.name = @currentLoginName
    )
    begin
    exec sp_addrolemember @currentRole, @currentLoginName
    end

    select @currentLoginName = 'mydomain\user6'
    if not exists
    (
    SELECT null --, role_principal_id, member_principal_id, princ1.name AS
    role_name, princ2.name As member_name
    FROM sys.database_role_members AS S LEFT JOIN sys.database_principals AS
    princ1 ON S.role_principal_id = princ1.principal_id AND princ1.type = 'R'
    LEFT JOIN sys.database_principals AS princ2 ON S.member_principal_id =
    princ2.principal_id AND princ2.type IN ('S', 'U')
    WHERE princ1.name = @currentRole and princ2.name = @currentLoginName
    )
    begin
    exec sp_addrolemember @currentRole, @currentLoginName
    end

    select @currentLoginName = 'mydomain\user1'
    if not exists
    (
    SELECT null --, role_principal_id, member_principal_id, princ1.name AS
    role_name, princ2.name As member_name
    FROM sys.database_role_members AS S LEFT JOIN sys.database_principals AS
    princ1 ON S.role_principal_id = princ1.principal_id AND princ1.type = 'R'
    LEFT JOIN sys.database_principals AS princ2 ON S.member_principal_id =
    princ2.principal_id AND princ2.type IN ('S', 'U')
    WHERE princ1.name = @currentRole and princ2.name = @currentLoginName
    )
    begin
    exec sp_addrolemember @currentRole, @currentLoginName
    end


    print '/@sp_addrolemember_return_value_total/'
    print @sp_addrolemember_return_value_total









    "UsenetUser" <> wrote in message
    news:...
    > VS 2005 clickonce deployment: What connection string do I use when I
    > deploy a windows forms application with an SQL Server DB? If I use sa
    > with no password, I get an error: login failed for user 'sa' Not
    > associated with a trusted SQL server connection. If I use integrated
    > security it fails, as I expect it would, because the user is unkown.
    >
    > Thank you.
     
    sloan, Aug 7, 2009
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ik
    Replies:
    42
    Views:
    551
    GrayWolf
    Jul 13, 2004
  2. Guest
    Replies:
    1
    Views:
    2,486
    DraguVaso
    Dec 13, 2004
  3. Guest
    Replies:
    2
    Views:
    274
    Guest
    Apr 26, 2006
  4. Andy Jump

    Sql Server Clr Assembly Deployment Time

    Andy Jump, Jul 4, 2006, in forum: Microsoft Dot NET
    Replies:
    2
    Views:
    160
    Andy Jump
    Jul 4, 2006
  5. Replies:
    0
    Views:
    170
Loading...

Share This Page