Vista - Deploying application with SQL server

I'm working on a VB.NET shareware program so naturally I will have no
knowledge of local users or credentials. It uses SQL Server for data
storage. In development, SQL Server 2005. I publish it with clickonce,
including SQL Express as a prerequiste, which installs ok on the
client machine. My problem is, after the install, I get the error: The
failed. Login failed for user 'Mycomputer\testuser'.

How do I get around this?

Thank you!

How the SQL Server Express is installed? That is, as regular instance
(default or named instance) or user instance via ClickOnce. It seems it is
former (if it is User Instance, you would not have that "login failed"
exception).

Obviously, then SQL server uses Windows integrated security. So, the use's
windows account must be mapped to a valid SQL Server login, which in turn
should be a user of targeting database with proper permissions.

If the SQL Server can be installed by "ClickOnce", it implies the user must
have the right to do this (local admin/domain admin). If so, unless the user
uses Vista, he/she should automatically has access to SQL Server.

Or are you saying the "prerequisite" is actually done before "ClickOnce"? In
this case, you should run some code (such as SQL script) to create needed
SQL Server login/database user. Of course, if you use windows security, you
need to get user's windows account name during installation. If the user is
in a network domain and you can expect all your users are in a given domain
security group, then you can simply include the user group in the script. Or
you can make the SQL Server uses SQL security and you can pre-create SQL
Server login and map it as needed database user. Then you app will always
log in as that SQL server login.

"UsenetUser" <spam@xxxxxx> wrote in message
news:5mnj955v66rltpnccbe5rbikpddij335ff@xxxxxx

Hi Norman,

Thanks for the response. Answers/comments in-line.

On Sun, 30 Aug 2009 06:57:43 -0700, "Norman Yuan"
<FakeName@xxxxxx> wrote:

After making some changes (no, I didn't log them yet). The error is:

System.Data.SqlClient.SqlException: An error has occurred while
establishing a connection to the server. When connecting to SQL
Server 2005, this failure may be caused by the fact that under the
default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a
connection to SQL Server)
Installed with Clickonce. I don't see any choices about instance, and
assume it would be 'default'.
I also played around with using sa login but couldn't make it work.
Test target machine is XP SP2. While this is my first effort, I just
cannot figure out why it is not working the way you describe. I've
come across one or two other posts where they also say the local
(administrators group members) users get rights and can connect to SQL
Express okay, although it seemed like they had the same problem as me
initially and had to play around with it for a while to make it work
(No details provided).

SQL Server does (seem to) install okay. The error is generated by .NET
as you can see.

Could it be that I am developing with SQL Server 2005? SQL Server is
running on the development machine. I tried another test with
'localhost' in the connection string ("Data Source=localhost;Initial
Catalog=PB2be;Integrated Security=True") - same problem.
Well, it is a prerequisite in clickonce's list of prerequisites, and
of course does get installed before the application file, but I don't
think that is part of the problem, because after the fail, if I try to
run setup again (SQL Express has already been installed at this point)
even after a reboot, the same thing happens.

Of course, if you use windows security, youThis is a shareware application!

It seems incredible to me that:

a) this is not a VERY common scenario (SQL Express deployed with app
via Clickonce to unknown target computers)

b) It would necessitate complex solutions like scripting the DB and/or
users, although I suppose worse things have happened. I really hope
there is a simpler solution.

//Quote
It seems incredible to me that:

b) It would necessitate complex solutions like scripting the DB and/or
users, although I suppose worse things have happened. I really hope
there is a simpler solution.
//End Quote


Why is this "incredible " to you?

Having scripts for your database and database objects promotes repeatability
and success.


You can find a basic example here using sqlcmd.
http://sholliday.spaces.live.com/Blo...842A!583.entry

Whether or not you use sqlcmd or not, you ~should have your database
scripts....especially if you want to guarantee repeatable results on a
client installation.

..................

Below is some more TSQL code for giving certain domain users the role of
'db_datareader'.
It adds the login, the username to the database and grants the role.

Again, its accomplished via code. If you want repeatable results, then code
it up.

http://www.pragprog.com/the-pragmati.../extracts/tips
Don't Use Manual Procedures
A shell script or batch file will execute the same instructions, in the same
order, time after time.
Don't Live with Broken Windows
Fix bad designs, wrong decisions, and poor code when you see them.
Don't Use Wizard Code You Don't Understand
Wizards can generate reams of code. Make sure you understand all of it
before you incorporate it into your project.




Use MyDatabase-- ON MyServer
GO






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

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


select @currentLoginName = 'mydomain\myuser1'

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\myuser2'

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\myuser1'
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\myuser2'
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_datareader'




select @currentLoginName = 'mydomain\myuser2'
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\myuser1'
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" <spam@xxxxxx> wrote in message
news:dkmn95l586o2fd9i33usipesbfoc9ng61i@xxxxxx

Norman,

If you (or anyone) is still interested, I have some further info:

- SQL Express Surface Area Configuration is configured to allow
remote connections and also SQL Browser service is running, not that
either seems to make any difference.
- Found several posts that say the instance has to be named in the
connection string. For SQL Express the default name is SQLEXPRESS.
Tried that, and now the error is:
System.Data.SqlClient.SqlException: Cannot open database "PB2be"
requested by the login. The login failed.
Login failed for user 'PB-F52F85AB2B50\Testuser'. (Testuser is a
member of administrators on the target machine)

Thanks.

I only visit this NG occasionally since my company's policy starts blocking
access to news group

It seems your SQL Server Express installation does install it as named
instance. That is, then SQL Server name is ComputerName\SQLEXPRESS. So, make
sure you app's ConnectionString refers the sql server name correctly.

It is not very clear: is it that the application uses
'PB-F52F85AB2B50\Testuser' account (that is, this user logged in and ran the
app) to access 'PB-F52F85AB2B50\SQLEXPRESS'? That is, the app and the SQL
Server Express sit inthe same box?

According to the error, it seems the app can connect to the SQL Server, but
the user account does map to a local admin (on WIN XP, not Vista) account.

IMO, for shareware using something like SQL Server Express makes thing a lot
complicated. Yes, as the other post said, the best approach would be the
have some SQL Scripts that run right after SQL Server installation to setup
database, create neccessary SQL Server Login/database users.

Since it is shareware and the app and the SQL Server Express is in the same
box, you may consider using SQL Server security, instead of Window security.
This way, only SQL Server installation requires local admin, afterward, the
app access the SQL Server/DB with SQL Server Login, no local admin right is
needed.



"UsenetUser" <spam@xxxxxx> wrote in message
news:73js959qdblor6j84gar7942if9bafg0l8@xxxxxx

//Quote from OP//
//> Login failed for user 'PB-F52F85AB2B50\Testuser'. (Testuser is aPlease review my (previous) post about coding up your security needs.
The info between --->>> and <<<--- is the most important part of it.

Here is a quote from my previous thread:
Below is some more TSQL code for giving certain domain users the role of
'db_datareader'.

--->>> It adds the **login**, the **username** to the database and grants
the role. <<<---
Again, its accomplished via code. If you want repeatable results, then code
it up.


There is more information at that (previously made) post.


Since you're running under a restricted account you need to
(programmatically preferred) add privileges and rights for your user(s).


"UsenetUser" <spam@xxxxxx> wrote in message
news:73js959qdblor6j84gar7942if9bafg0l8@xxxxxx