PC Review


Reply
Thread Tools Rate Thread

SQL Server connection string for deployment ?

 
 
UsenetUser
Guest
Posts: n/a
 
      7th Aug 2009
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.
 
Reply With Quote
 
 
 
 
sloan
Guest
Posts: n/a
 
      7th Aug 2009

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
An error has occurred while establishing a connection to the server.When connecting to SQL Server 2005, this failure may be caused by the factthat under the default settings SQL Server does not allow remote connections. mina Microsoft VB .NET 0 8th Oct 2008 07:12 AM
An error has occurred while establishing a connection to the server.When connecting to SQL Server 2005, this failure may be caused by the factthat under the default settings SQL Server does not allow remote connections. mina Microsoft Dot NET 0 8th Oct 2008 07:11 AM
Server Reference In SQL Server 2005 Connection String Joey Microsoft ASP .NET 3 18th Jan 2006 05:41 PM
Enterprise Library deployment problem. Why needs to InstallUtil DLLs to the deployment server? If not, Registry Error whould shown. Benny Ng Microsoft ASP .NET 0 3rd Oct 2005 06:40 AM
How do i change this connect string to support a SQL Server 2000 running on port 8832 User ID=car;Password=rat;Server=abc.def.hij;Initial Catalog=foobar i tried Server=abc.def.hij:8823 and Server=abc.def.hij;port=8823 neither of these work Daniel Microsoft Dot NET Framework 1 16th Dec 2004 10:55 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 11:17 AM.